cpy last 7 rows of data

[redspanna]

Hi all

In sheet 1..find last popoulated row in column AL, copy up 7rows of data then paste into sheet2 AH8:AH14

any ideas

thanks so much

 

[VoG]

Try

Sheets("Sheet1").Range("AL" & Rows.Count).End(xlUp).Offset(-6).Resize(7).Copy Destination:=Sheets("Sheet2").Range("AH8")

 

[redspanna]

played around with some other vb and kinda got this to work

Sub Anthony()


Sheets("sheet1").Select

    Range("AL65536").End(xlUp).Offset(-7).Select
    Selection.Resize(Selection.Rows.Count + 7).Select
    Selection.Copy
    Sheets("sheet2").Select
    Range("AH8").Select
    Selection.PasteSpecial Paste:=xlValues
End Sub

thanks though

 

[Peter_SSs]

played around with some other vb and kinda got this to work

Sub Anthony()


Sheets("sheet1").Select

    Range("AL65536").End(xlUp).Offset(-7).Select
    Selection.Resize(Selection.Rows.Count + 7).Select
    Selection.Copy
    Sheets("sheet2").Select
    Range("AH8").Select
    Selection.PasteSpecial Paste:=xlValues
End Sub

thanks though

Except ...

a) That does not do what was originally requested. The original requirement was to copy 7 rows in to AH8:AH14 of sheet2, whereas this copies 8 rows into AH8:AH15, and

b) The code has a whole lot of selections that are not required and just slow your code.

If you really want 8 rows then just make this change to VoG's code and you have it in one efficient line

Sheets("Sheet1").Range("AL" & Rows.Count).End(xlUp).Offset(-7).Resize(8).Copy Destination:=Sheets("Sheet2").Range("AH8")