Copying from one sheet and pasting to the next empty row is no longer working

[berm007]

Hi guys and gals... I am having a nightmare and need some help!

I am trying to filter my data by a specific column (in this case Column 'D') and then copy the filtered data to another worksheet in the same workbook.

I have basically copied and pasted some VBA script I used on another project that did this exact task and the code worked there but for some reason will not work here! The debugger is pointing me towards the final 2 lines in the code but I have no idea what's going wrong... Any help would be amazing!!! I'm using office 2016 if that helps...

Worksheets("Sheet1").Range("D:D").AutoFilter _
    Field:=4, _
    Criteria1:="AMEX", _
    Operator:=xlAnd

Worksheets("Sheet1").UsedRange.Columns("A:C").Offset(1).Copy _
    Destination:=Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)

 

[Peter_SSs]

You didn't say what the actual error message was, but my guess is that Excel thinks Sheet1's used range goes all the way to the bottom of the worksheet. If that was the case then
UsedRange.Columns("A:C").Offset(1)
would be trying to copy something that was off the bottom of the worksheet.

Add this line above that code and see what it tells you when the code runs.

MsgBox Sheets("Sheet1").UsedRange.Rows.Count
Worksheets("Sheet1").Range("D:D").AutoFilter _
    Field:=4, _
    Criteria1:="AMEX", _
    Operator:=xlAnd

Worksheets("Sheet1").UsedRange.Columns("A:C").Offset(1).Copy _
    Destination:=Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)

If that does not help you solve the problem then tell us what the actual error message you got was.