Copy Paste filtered column ok in 2003 not in 2007

[Sandra Williams]

In Excel 2003 I copy a range of columns from a filtered datain one workbook
and paste into another in a number of macros.
In 2007 I get the "can't paste because paste and copy area are not the same
shape" error message. I can find no way round this. If I manually copy a
whole column which has autofilter on (and is filtered in some way), I cannot
paste that column into another sheet.

There must be a work-a-round - please can you help?

 

[NoodNutt]

G'day Sandra

Are you copying the entire column from one sheet, then attempting to patse
into a defined range.

eg

Sheets("Sheet1").Select
Range("A:A").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1:A100").Select
Selction.Paste

Check your code to make sure you don't have something like this, if so,
correct as required.

HTH
Mark.

 

[Sandra Williams]

Mark,
I was doing this column-to-column - it isn't just in a macro, this doesn't
work manually either. With some experimenting I found that I could copy if I
specified the first and last cells e.g. not Range ("A:B") but
Range("A1:B4320").

BUT Something seems to have changed in the way filtering is handled. For
instance, manually auto filter a column in a spreadsheet. Manually copy the
first cell to the last cell (not selecting the whole column - either
CTRL+Shift+end or scroll), paste it into another spreadsheet. You will get
the unfiltered contents. However, if you had copied Row 2 onwards (not
copying the filtered row) you would have got the filtered contents!

Do you think there might be an option somewhere in 2007 to alter the
behaviour of copying with filtered lists? I've tried the main microsoft
pages but can't see anything relevent.

Sandra

 

[NoodNutt]

Sandra

I think what is happening is that the first row is being treated as the
header row.

You may have to move your data down a row and use the first row as the
header, either with or without text headings.

See if that makes a difference.

HTH
Mark