Range.AutoFilter Method (Excel)

Question

I'm using code like this:

Sheets("1").Select
Range("A1:Z100").Select
Selection.Autofilter
Worksheets("Sheet1").Range("$A$1:$Z$100").AutoFilter field:=1, Criteria1:=String1
Worksheets("Sheet1").Range("$A$1:$Z$100").AutoFilter field:=2, Criteria1:=String2
Range("A1:Z100").Select
Selection.Copy
Sheets("2").Select
Range("A1").Select
ActiveSheet.Paste

(Where String1 and String2 are defined previously)

I use this code inside a loop, and it works most of the time. However, sometimes it doesn't...

When there are no results that match both criteria it's giving me everything inside the filtered range when it pastes, rather than pasting empty cells, as I'd like.

The really annoying part is that I have more-or-less identical code in another spreadsheet the works precisely like this - giving me nothing when there are no hits to the query.

Answer 1

' Give it a try after removing the "$" sign.

Sheets("1").Select
Range("A1:Z100").Select
Selection.Autofilter
Worksheets("Sheet1").Range("A1:Z100").AutoFilter field:=1, Criteria1:=String1
Worksheets("Sheet1").Range("A1:Z100").AutoFilter field:=2, Criteria1:=String2

Range("A1:Z100").SpecialCells(xlCellTypeVisible).Select

Selection.Copy
Sheets("2").Select
Range("A1").Select
ActiveSheet.Paste

' U must copy the cell which is visible