Office 2007 Copy Paste Issue

[anshuman]

Hi All,

I wrote a macro in Office 2003 which does the following task.

In a data Sheet names Total2, it filters Colum 2 using a keyword and
then copies only the filtered data onto a new sheet called
Calculation.

I used the following lines of code which was working perfectly

Sheets("Total2").Select
Selection.AutoFilter Field:=3, Criteria1:=Var3 'Var 3 keeps on
changing for each loop I run
Sheets("Total2").Select
Range("A1:FE999").Select
Selection.Copy
Sheets("Calculation").Select
Range("A1").Select
ActiveSheet.Paste

Earlier, it was copying only the filtered data...

Now, when I try running the same macro using Office 2007, the entire
data is copied irrespective of what I use as a filter.

What do I do to paste only the filtered data in the Sheet
"Calculation"

Please help

Thanks in advance,
Anshuman

 

[squenson via OfficeKB.com]

Unfortunately I do not have Excel 2007, but I would record manually a macro
that does a copy of a filtered range, then compare the code with the macro
and see where the differences are.

Stephane.

 

[anshuman]

I tried doing so...Excel 2007 copies the entire daat sheet up to the
last row in the filter...how do I just copy what is visible after
filtering in Office 2007?

 

[Ron de Bruin]

Try the basic AutoFilter macro here
http://www.rondebruin.nl/copy5.htm

Set the filter range first in the macro

Then you can use AutoFilter.Range to copy
Also you not have to use select in the macro

 

[anshuman]

Hi Ron,

I used this:

Sheets("Total2").Select
Selection.AutoFilter Field:=3, Criteria1:=Var3
Dim WS As Worksheet
Dim WSNew As Worksheet
Set WS = Sheets("Total2")
Set WSNew = Sheets("Calculation")
WS.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With

Doesn't help..It's just copying everything....Any idea what is
happening?

Thanks in advance,
Anshuman

 

[Ron de Bruin]

This is working OK for me if your data start in A and there are no empty rows or columns in the range
You can also use the example from my site to set the range

Sub AAA()
Dim WS As Worksheet
Dim WSNew As Worksheet

Set WS = Sheets("Total2")
Set WSNew = Sheets("Calculation")

WS.AutoFilterMode = False

WS.Range("a1").CurrentRegion.AutoFilter Field:=3, Criteria1:="ron"
WS.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

WS.AutoFilterMode = False

End Sub