Copy filtered data with gaps to new worksheet.

[Horizon24]

Hi,

I have filtered data that will change in volume on a weekly basis, i cant figure out how to copy the data down to the last record including all the gaps in the data (keeping the gaps) and paste into a new worksheet. The macro i've been using originally...

Sheets("New Load").Select
Range("A1:D1").Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Amendments").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

only selects the data until the first blank space when i need it to continue to the bottom as there is more data beneath.

Any help?

 

[JLGWhiz]

try this instead.

Dim sh As Worksheet, lr As Long
Set sh = Sheets("New Load")
lr = sh.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
sh.Range("A2:D" & lr).Copy
Sheets("Amendments").Range("A4").PasteSpecial xlPasteValues

 

[Horizon24]

Thank you very much! Works perfectly!

 

[JLGWhiz]

Thank you very much! Works perfectly!

You're welcome,
Regards, JLG