Office 2007 Copy Paste Issue
 

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

Office 2007 Copy Paste Issue
 

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 wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200708/1

Office 2007 Copy Paste Issue
 

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?


On Aug 18, 5:30 pm, "squenson via OfficeKB.com" <u36146@uwe wrote:
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 wrote:
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

--
Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/ms-excel/200708/1

Office 2007 Copy Paste Issue
 

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


--

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


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

Office 2007 Copy Paste Issue
 

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

Office 2007 Copy Paste Issue
 

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