Macros, Objects, and Pasting... Help!!!

[blooka241]

I'm trying to write a macro that will locate and copy the last row of information in a worksheet and paste it into another worksheet. This is what I've written so far:

Dim counter As Integer
For counter = 1 To 25
i = counter - 1
If Cells(1, counter) < 1 Then Range("Ai:Fi").Select
Selection.Copy
Worksheets("Sheet2").Activate
Worksheets("Sheet2").Range("K2:P2").Select
Range("K2:P2").Select
ActiveSheets.Paste
Next counter
End

When I run the debugger, I get runtime error 424 "Object required"

Any suggestions??? Thanks so much in advance for your help!

 

[VoG]

Possibly

Sub xxx()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & LR & ":F" & LR).Copy Destination:=Sheets("Sheet2").Range("K2")
Application.CutCopyMode = False
End Sub

 

[blooka241]

VoGII,

Thanks for the reply! The code works like a charm!

Can I use the same range you used to copy data to paste the data (rather than just K2) in order to paste new data under the previously pasted data (ie the first set is pasted to K2 the next to K3 etc.)?

Thanks again for all your help!!

 

[VoG]

Try

Sub xxx()
Dim LR1 As Long, LR2 As Long
LR1 = Range("A" & Rows.Count).End(xlUp).Row
LR2 = Sheets("Sheet2").Range("K" & Rows.Count).End(xlUp).Row
Range("A" & LR1 & ":F" & LR1).Copy Destination:=Sheets("Sheet2").Range("K" & LR2 + 1)
Application.CutCopyMode = False
End Sub

 

[blooka241]

Works great!!! Exactly what I needed, many many thanks!!!