| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy & Paste Issue | Excel Discussion (Misc queries) | |||
| Paste Value Option change in Office 2007 | Excel Discussion (Misc queries) | |||
| Copy Paste issue | Excel Worksheet Functions | |||
| Issue with copy/paste dates from filtered data | Excel Discussion (Misc queries) | |||
| Issue with copy & paste? | Excel Discussion (Misc queries) | |||
Linear Mode
