Macro Copy & Paste?

[redart]

I've got the Macro blues. I'm trying to get VBA to insert a new line at the foot of a Worksheet range, then copy the contents of the last line into the newly created blank line. Problem is trying to make this repeatable, as when I run the macro a second time, all the cell references point to the line number which was active when I recorded the macro, and it simply overwrites the previous line. Can anyone suggest a way to increment the line numbers used each time a macro is run?
Thanks. Tony

 

[Andrew Poulsom]

Something like this:

Sub Test()
    Dim Rng As Range
    Set Rng = Range("A65536").End(xlUp).EntireRow
    Rng.Copy Rng.Offset(1, 0)
End Sub
[CODE]

It finds the last used cell in column A and expands to cover all columns. Then it copies the contents one row down.

 

[redart]

Thanks Andrew. I'm a newcomer to BB's (I think yesterday was the first time I've posted), but I'll definitely be using them more often from now. Your reply got me on the right track. I needed to insert a blank line before copying the last line down (due to other working cells below), and then I needed to clear some of the cells in the new line, as well as a couple in the previous line. The final code looks like this:-

Sub Test()
Dim Rng As Range
Set Rng = Range("A65536").End(xlUp).EntireRow
Rng.Offset(1, 0).Activate
ActiveCell.EntireRow.Insert
Rng.Copy Rng.Offset(1, 0)
Rng.Cells(2, 17).ClearContents
Rng.Cells(2, 25).ClearContents
Rng.Range(Cells(2, 28), Cells(2, 32)).ClearContents
Rng.Range(Cells(2, 46), Cells(2, 47)).ClearContents
Rng.Range(Cells(2, 51), Cells(2, 52)).ClearContents
Rng.Range(Cells(1, 33), Cells(1, 36)).ClearContents
End Sub

Probably not as concise as possible, but I'm new to VBA also, having previously just use record, so happy to get my first real code running, and working properly. Thanks again.