Pasting filtered list

[andrewgibsonsw]

I have a macro that uses AutoFilter to filter a list on one sheet. I then use
Range("A1").CurrentRegion.Copy
Sheets("Calculation").Select
Range("A4").Select
ActiveSheet.Paste
to paste the filtered data to a second sheet.
However, I receive an error message saying that the Method Paste failed even though
the data does seem to be copied correctly.
I've tried using Activate rather than Select for the Calculation sheet, but still receive
the error message.
What am I doing wrong? Thanks, Andy.

[rory]

Is it any better if you use:

Range("A1").CurrentRegion.Copy Sheets("Calculation").Range("A4")

instead?

[andrewgibsonsw]

[quote name='rory' post='792087' date='04-Sep-2009 10:50']Is it any better if you use:

Range("A1").CurrentRegion.Copy Sheets("Calculation").Range("A4")

instead?[/quote]
Immediate response! But no, I tried that already. Andy.

[rory]

Which version of Excel and do you have any hidden columns, or around 8000 discontiguous rows due to the filter?

[andrewgibsonsw]

[quote name='rory' post='792089' date='04-Sep-2009 10:59']Which version of Excel and do you have any hidden columns, or around 8000 discontiguous rows due to the filter?[/quote]
Excel 2003. There are no hidden columns and only a few rows currently.
The filtered data was originally from an external source, but this shouldn't make any
difference should it? Andy.

[rory]

No it shouldn't. Can you post the workbook (removing any confidential info)?

[andrewgibsonsw]

[quote name='rory' post='792091' date='04-Sep-2009 11:17']No it shouldn't. Can you post the workbook (removing any confidential info)?[/quote]
Attached.
I've got it to work using Advanced Filter, but would prefer that the first version worked.
Thanks, Andy.

[rory]

Your first version works fine for me in 2003! Which SPs do you have installed? (I'm only on SP2 here)

[andrewgibsonsw]

[quote name='rory' post='792094' date='04-Sep-2009 12:10']Your first version works fine for me in 2003! Which SPs do you have installed? (I'm only on SP2 here)[/quote]
I'm using a version without Service Packs. Perhaps I should use the Advanced Filter
version - they always seem more robust that using AutoFilter. Andy.

[rory]

In my experience, Autofilters work much better in code if you specify the actual range they should work with rather than using just the first row or first cell to set it up for example.