Use VBA to duplicate a row structure/formulas?

  • Thread starter Thread starter cronoklee
  • Start date Start date
C

cronoklee

Hi all,
I'm looking for an automated way to duplicate the last row in a
worksheet as soon as it has been edited. I need the new row to be
empty but maintain the formulas of the previous row. Is this possible
please? I'm working on a continuous form for other users so there's no
way of knowing how many rows will be needed in advance. I've never
done anything like this in excel before so I'm not even sure if it's
possible!

Thanks a lot,
Ciarán
 
I do something similar in a "Logbook" file I made for our shop. What I did
was create the formulas and formatting I wanted in Row 1, and then hid that
row and put in a freeze line about row12, and started the database
there....then as each new row is added, I simply have row 1 copied down to
the new row.

hth
Vaya con Dios,
Chuck, CABGx3
 
I do something similar in a "Logbook" file I made for our shop.  What Idid
was create the formulas and formatting I wanted in Row 1, and then hid that
row and put in a freeze line about row12, and started the database
there....then as each new row is added, I simply have row 1 copied down to
the new row.
Click to expand...

OK cheers - sounds like a good plan but how do I copy row 1 into the
new row using code?
Ciaran
 
This is the original macro I used.....crude, of course, from 5 years ago, but
it's still working today......."ROWFORMAT" is a named range of the number of
columns in row1 that I wanted to copy.......


Sub RowFormat()
'=====================================================
' RowFormat Macro
' Macro created 1/25/2004 by Chuck Roberts
' Takes the user to the bottom of the database
' and pre-formats all columns of the next blank row
' to facilitate manual data entry....(template is hidden row #1), range
' named ROWFORMAT
' modified 2/15/04 by CLR to increment Original Item column A by 1
' modified 2/20/04 by CLR to increment the Workorder column B by 1 and
' clear the automatic "CAP" value from column C
' modified April2004 to remove the workorder number incrementing (see REM
' below)
' modified 12/1/04 to reinstall the workorder number incrementing(just
un-REMed
' that line of code)
'=====================================================
Application.GoTo Reference:="R1C1"
Application.GoTo Reference:="RowFormat"
Selection.Copy
Application.GoTo Reference:="R8C1"
Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste

Application.GoTo Reference:="R8C1"
Selection.End(xlDown).Select
Selection.Offset(1, 0).Value = Selection.Value + 1 'adds one to Original
Item column A
Selection.Offset(1, 1).Select
Selection.Offset(-1, 0).Select
'this item REM'ed out because of the new dash numbers on column B
're-activated 12/1/04
Selection.Offset(1, 0).Value = Selection.Value + 1 'adds one to
workorder column B
Selection.Offset(1, 1).Select

End Sub


You should be able to pick out what you want from there.....watch out for
the email word-wrap

Vaya con Dios,
Chuck, CABGx3
 
This is the original macro I used.....crude, of course, from 5 years ago,but
it's still working today......."ROWFORMAT" is a named range of the numberof
columns in row1 that I wanted to copy.......

Sub RowFormat()
'=====================================================
' RowFormat Macro
' Macro created 1/25/2004 by Chuck Roberts
' Takes the user to the bottom of the database
' and pre-formats all columns of the next blank row
' to facilitate manual data entry....(template is hidden row #1), range
' named ROWFORMAT
' modified 2/15/04 by CLR to increment Original Item column A by 1
' modified 2/20/04 by CLR to increment the Workorder column B by 1 and
' clear the automatic "CAP" value from column C
' modified April2004 to remove the workorder number incrementing (see REM
' below)
' modified 12/1/04 to reinstall the workorder number incrementing(just
un-REMed
' that line of code)
'=====================================================
    Application.GoTo Reference:="R1C1"
    Application.GoTo Reference:="RowFormat"
    Selection.Copy
    Application.GoTo Reference:="R8C1"
    Selection.End(xlDown).Offset(1, 0).Select
    ActiveSheet.Paste

    Application.GoTo Reference:="R8C1"
    Selection.End(xlDown).Select
    Selection.Offset(1, 0).Value = Selection.Value + 1 'adds one toOriginal
Item column A
    Selection.Offset(1, 1).Select
    Selection.Offset(-1, 0).Select
    'this item REM'ed out because of the new dash numbers on column B
    're-activated 12/1/04
    Selection.Offset(1, 0).Value = Selection.Value + 1 'adds one to
workorder column B
    Selection.Offset(1, 1).Select

End Sub

You should be able to pick out what you want from there.....watch out for
the email word-wrap

Vaya con Dios,
Chuck, CABGx3
Click to expand...


Looks great Chuck but I'm a bit of a n00b with vba in excel. I've only
every done vba stuff in access so I'm not sure how to attach the macro
to the afterupdate event of the previous row. When I run the code
manually, I get a 400 error(??).

I've uploaded a really basic test file to my webspace so if you have a
few mins free, perhaps you could take a look for me?
http://www.crondesign.com/shared/sample.zip
Thanks a million!
Ciaran
 
I cannot open a .xlsm file.
You can call a macro from outside a change-event macro by using the line
Call RowFormat
or
Call WhateverTheMacrosNameIs

The reason you are getting an error running it byitself is probably because
you did not name the area of row1 that you are interested in the rangename
ROWFORMAT.

Vaya con Dios,
Chuck, CABGx3
 
Hey thanks a lot Chuck,
Got it sorted in the end with the help of your function.
I appreciate the help!
Ciarán
 
Back
Top