Macro to add more lines
 

I need to create a macro, attached to a button that will go to the last line
in col B and copy that line down one row so that all the formulas in the
last line form col's B through G are copied down to the next row.

I have created the button but I don't know how to create the macro.

Thank you for any help.

Macro to add more lines
 

This will do the trick

Private Sub CommandButton1_Click()

Dim CurRow As Integer
Dim ColLtr(5) As String
Dim CurCol As Integer

ColLtr(0) = "B"
ColLtr(1) = "C"
ColLtr(2) = "D"
ColLtr(3) = "E"
ColLtr(4) = "F"
ColLtr(5) = "G"

Application.ScreenUpdating = False
For CurCol = 0 To UBound(ColLtr)

CurRow = 0
Debug.Print " The currently selected column is " & _
ColLtr(CurCol)
CurRow = CurRow + 1
Range(ColLtr(CurCol) & CurRow).Select
Do Until Selection = ""
Range(ColLtr(CurCol) & CurRow).Select
CurRow = CurRow + 1
Loop
Range(ColLtr(CurCol) & CurRow).Offset(-1, 0) = _
Range(ColLtr(CurCol) & CurRow).Offset(-2, 0)

Next CurCol
Application.ScreenUpdating = True

End Sub

Macro to add more lines
 

One way:

Private Sub CommandButton1_Click()
Dim LastRow As Long
Application.ScreenUpdating = False
LastRow = Range("B65536").End(xlUp).Row
Range("B" & LastRow & ":G" & LastRow).Copy _
Range("B" & LastRow + 1)
Application.ScreenUpdating = True
End Sub

This assumes all the columns have data down to the same row.

Regards

Trevor


"Frick" wrote in message
...
I need to create a macro, attached to a button that will go to the last
line in col B and copy that line down one row so that all the formulas in
the last line form col's B through G are copied down to the next row.

I have created the button but I don't know how to create the macro.

Thank you for any help.

Macro to add more lines
 

Mark,

Thanks for the reply. I added the script and attached to Command Button 1.
When I clicked on the button the script executed but stopped at;

Range(ColLtr(CurCol) & CurRow).Offset(-1, 0) = _
Range(ColLtr(CurCol) & CurRow).Offset(-2, 0)

I am not sure why that happened. Presently the worksheet I created has data
in Col B from row 12 through 27. When I click on the button it should go to
row 27 and copy a new row to row 28, copying col's B through G.

Can you further assist.

Frick

"Mark" wrote in message
oups.com...
This will do the trick

Private Sub CommandButton1_Click()

Dim CurRow As Integer
Dim ColLtr(5) As String
Dim CurCol As Integer

ColLtr(0) = "B"
ColLtr(1) = "C"
ColLtr(2) = "D"
ColLtr(3) = "E"
ColLtr(4) = "F"
ColLtr(5) = "G"

Application.ScreenUpdating = False
For CurCol = 0 To UBound(ColLtr)

CurRow = 0
Debug.Print " The currently selected column is " & _
ColLtr(CurCol)
CurRow = CurRow + 1
Range(ColLtr(CurCol) & CurRow).Select
Do Until Selection = ""
Range(ColLtr(CurCol) & CurRow).Select
CurRow = CurRow + 1
Loop
Range(ColLtr(CurCol) & CurRow).Offset(-1, 0) = _
Range(ColLtr(CurCol) & CurRow).Offset(-2, 0)

Next CurCol
Application.ScreenUpdating = True

End Sub