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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"anshuman" wrote in message oups.com...
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


On Aug 18, 7:58 pm, "Ron de Bruin" wrote:
Try the basic AutoFilter macro herehttp://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

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"anshuman" wrote in oglegroups.com...
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