Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter and VBA
Hey all,
I was wondering if you could help me with a VBA problem. I need to program a macro which would essentially Autofilter a large amount of information based on two criteria. The critera is listed in two cells (in cells on another tab/worksheet named "Claims" - cells G2 and G3.) The values in these cells match entries in two of the columns being filtered. So, for example, cell G2 in worksheet "Claims" = "Auto" and cell G3 = "NJ" - I need the program to filter the data in my other worksheet ("Data") by the two cvalues listed above. Once I get the filter to work, I need to copy all this data and paste it into a new workbook. I hope i explained the above well enough to allow you to help me. If you have any further questions, please ask. Thank you in advance. -Haas |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter and VBA
JW,
Won't this copy the ENTIRE worksheet? The OP only wanted the filtered data to be copied? You used the line: .UsedRange.Copy ws.Range("A1") -- Regards, Bill Renaud |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter and VBA
On Oct 10, 3:01 am, "Bill Renaud"
wrote: JW, Won't this copy the ENTIRE worksheet? The OP only wanted the filtered data to be copied? You used the line: .UsedRange.Copy ws.Range("A1") -- Regards, Bill Renaud Bill, actually no. It will only copy the filtered data. It's one of those crazy Excel things where it seems like one bit of code would cause one thing to happen, but it actually works for some reason. Give it a go and let me know if it works for you. Regards -Jeff- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter and VBA
Yep, it works in Excel 2000 (SP-3) (copying filtered data)!
I am VERY skeptical of this "feature" (bug?), though. This behavior is not documented in either the UsedRange property or the Copy method! The caveat is that it only works if the rows were hidden by using AutoFilter. If the rows were hidden using the normal Format|Row|Hide command, then ALL of the data is copied, as I first mentioned. This is true, even though AutoFilter essentially works by simply hiding the rows not wanted! -- Regards, Bill Renaud |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter and VBA
On Oct 11, 2:01 am, "Bill Renaud"
wrote: Yep, it works in Excel 2000 (SP-3) (copying filtered data)! I am VERY skeptical of this "feature" (bug?), though. This behavior is not documented in either the UsedRange property or the Copy method! The caveat is that it only works if the rows were hidden by using AutoFilter. If the rows were hidden using the normal Format|Row|Hide command, then ALL of the data is copied, as I first mentioned. This is true, even though AutoFilter essentially works by simply hiding the rows not wanted! -- Regards, Bill Renaud Thanks guys! JW, the code worked perfectly - it achieved the desired results. I appreciate all the help and I'll make sure to keep the help going for others. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2007 autofilter change to 2003 autofilter functionality? | Excel Discussion (Misc queries) | |||
2007 excel autofilter back to 2003 autofilter? | Excel Discussion (Misc queries) | |||
2007 excel autofilter change back to 2003 autofilter? | Excel Discussion (Misc queries) | |||
2007 Autofilter worse than 2003 Autofilter | Excel Discussion (Misc queries) | |||
How to Sort within AutoFilter with Protection on (and AutoFilter . | Excel Discussion (Misc queries) |