Hi Chris

That is the way Autofilter works.
When you have filtered a range, only the visible rows are copied.
If you want your sheet 2 to have only the rows required, with blanks to
the non required rows, then copy the whole of sheet 1 to sheet 2.
Suppose you only wanted rows where column C values were = "Fred"
On Sheet 2, use Autofilter to select the opposite of the filter you used
previously e.g column C Not Equal to "Fred" as oppose to Equal to "Fred"
Now, mark the range of data that is visible and press Delete.
Data>Filter>Show All and you will have blanks rows where the data did
not match, and the data you require still showing in exactly the same
location as previously.