Excel 2007 - Autofilter - copy visible cells in columns - working in 2003 problem in 2007

[Ralph[1980PL]]

Hi All,

I went through the threads and I couldn't find the solution which could be helpful so...maybe someone;P will be able to find the way how to deal with the issue I encountered when I switched from excel 2003 to 2007.

Macro is located in workbook 1 and it opens another one (workbook 2) where it should auto filter data in some columns and then copy particular columns and paste them (only visible cells) in the workbook 1. Sounds very simple but...it's not:P. When I was using excel 2003 it worked smoothly but now the values which I get are blanks. In Debug mode I can't find any issue. Before you ask me...the autofilter code works fine because in other part of the macro in which it calculates the number of visible cells it gives correct values.

This is the code for copy paste.:

'copy from 8th column
xlApp.Worksheets("Main").AutoFilter.Range.Columns(8).SpecialCells(xlCellTypeVisible).Select
xlApp.Worksheets("Main").AutoFilter.Range.Columns(8).SpecialCells(xlCellTypeVisible).Copy
'paste in 1st column starting with A1
Worksheets("TEMP").Activate
Worksheets("TEMP").Range("A1").Select
Worksheets("TEMP").Paste

I will be really grateful if you could help me with this one.

Thanks in advance

 

[Datsmart]

AutoFilter "selection" includes invisible cells in 2007
Workaround is:
Apply the filter, cancel the filter, re-apply and it will work like version 2003.

Discussed here:
http://www.mrexcel.com/board2/viewtopic.php?t=304584

 

[Norie]

Ralph

You should avoid using Select/Activate, you might also want to look at using Advanced Filter rather than AutoFilter.