filted data, copy and paste a col. puts data in wrong row how fix

[chris_fig]

Ran into an issue when using Auto filter. copy cells in a col. from non seq.
rows, copy and then paste into a different col. and the data will paste in
seq rows and not as displayed on the screen. pastes in the correct col.
incorrect row.

 

[Roger Govier]

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.