Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code to replace Advanced Filter
I am applying advanced filter to determine different numbers from my data so
I can paste the result to a different cell in another sheet. I am using the following code (only 2 parts shown), to filter the data (using advanced filter) and this process has to occur many times (132) with changes to the filters (cells A2 to D2) and the cells where the results need to go. (In the code, Range C6 holds a Subtotal formula that counts the data in that column). I was wondering if there is a way to get my 132 different amounts without having to go through this process and instead use some code to obtain the various results and paste those results into the appropriate places in the MonthStats sheet. The FileFolDB data changes each month so the process needs to be repeated each month. Rob Sheets("FileFolDB").Select Range("A2") = "NSW" Range("B2") = "Member" Range("C2") = "=1" Range("D2") = "<=99" Range("A7:C30000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("A1:D2"), Unique:=False Range("C6").Copy Sheets("MonthStats").Select Range("D3").Select Selection.PasteSpecial Paste:=xlPasteValues Sheets("FileFolDB").Select Range("A2") = "NSW" Range("B2") = "Subscriber" Range("C2") = "=1" Range("D2") = "<=99" Range("A7:C30000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("A1:D2"), Unique:=False Range("C6").Copy Sheets("MonthStats").Select Range("E3").Select... Selection.PasteSpecial Paste:=xlPasteValues etc............ |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code to replace Advanced Filter
Does this help?
Sub advfilter() FilterIt "NSW", "Member", "=1", "<=99", "D3" FilterIt "NSW", "Subscriber", "=1", "<=99", "E3" 'etc. End Sub Private Sub FilterIt(crit1, crit2, crit3, crit4, target) With Sheets("FileFolDB") .Range("A2").Value = crit1 .Range("B2").Value = crit2 .Range("C2").Value = crit3 .Range("D2").Value = crit4 .Range("A7:C30000").AdvancedFilter Action:=xlFilterInPlace, _ CriteriaRange:=.Range("A1:D2"), _ Unique:=False .Range("C6").Copy Sheets("MonthStats").Range(target) End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "RobN" wrote in message ... I am applying advanced filter to determine different numbers from my data so I can paste the result to a different cell in another sheet. I am using the following code (only 2 parts shown), to filter the data (using advanced filter) and this process has to occur many times (132) with changes to the filters (cells A2 to D2) and the cells where the results need to go. (In the code, Range C6 holds a Subtotal formula that counts the data in that column). I was wondering if there is a way to get my 132 different amounts without having to go through this process and instead use some code to obtain the various results and paste those results into the appropriate places in the MonthStats sheet. The FileFolDB data changes each month so the process needs to be repeated each month. Rob Sheets("FileFolDB").Select Range("A2") = "NSW" Range("B2") = "Member" Range("C2") = "=1" Range("D2") = "<=99" Range("A7:C30000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("A1:D2"), Unique:=False Range("C6").Copy Sheets("MonthStats").Select Range("D3").Select Selection.PasteSpecial Paste:=xlPasteValues Sheets("FileFolDB").Select Range("A2") = "NSW" Range("B2") = "Subscriber" Range("C2") = "=1" Range("D2") = "<=99" Range("A7:C30000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("A1:D2"), Unique:=False Range("C6").Copy Sheets("MonthStats").Select Range("E3").Select... Selection.PasteSpecial Paste:=xlPasteValues etc............ |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code to replace Advanced Filter
Hi Bob,
Thank you! This has the makings of something I can use, but I'm having some trouble understanding it fully so that I can modify it to suit. The line ..... ..Range("C6").Copy Sheets("MonthStats").Range(target) is a mystery to me. I guess this line transfers the value from FileFolDB Range("C6") to a cell in the MonthStats sheet. But how does this line know which cell to paste the value to? Amazingly (to me anyhow), it's going to the right cell, but even though the right value is pasted, so is the formula. This confuses me as the formula in that pasted cell now references the column in the MonthStats sheet giving a circular ref error, as within that same column, I have some other formulas. If I delete those other formulas, the value in the cell that had the right value becomes 0. 1. I think a fix for this is that the Value and not the formula is pasted, but I don't know how to modify that line. 2. Could you also please advise how your code determines which cell to put the C6 value to. ( I guess it has something to do with Range(target), but where has that target range been set and what makes the code move from one cell to the next, etc.? Rob "Bob Phillips" wrote in message ... Does this help? Sub advfilter() FilterIt "NSW", "Member", "=1", "<=99", "D3" FilterIt "NSW", "Subscriber", "=1", "<=99", "E3" 'etc. End Sub Private Sub FilterIt(crit1, crit2, crit3, crit4, target) With Sheets("FileFolDB") .Range("A2").Value = crit1 .Range("B2").Value = crit2 .Range("C2").Value = crit3 .Range("D2").Value = crit4 .Range("A7:C30000").AdvancedFilter Action:=xlFilterInPlace, _ CriteriaRange:=.Range("A1:D2"), _ Unique:=False .Range("C6").Copy Sheets("MonthStats").Range(target) End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "RobN" wrote in message ... I am applying advanced filter to determine different numbers from my data so I can paste the result to a different cell in another sheet. I am using the following code (only 2 parts shown), to filter the data (using advanced filter) and this process has to occur many times (132) with changes to the filters (cells A2 to D2) and the cells where the results need to go. (In the code, Range C6 holds a Subtotal formula that counts the data in that column). I was wondering if there is a way to get my 132 different amounts without having to go through this process and instead use some code to obtain the various results and paste those results into the appropriate places in the MonthStats sheet. The FileFolDB data changes each month so the process needs to be repeated each month. Rob Sheets("FileFolDB").Select Range("A2") = "NSW" Range("B2") = "Member" Range("C2") = "=1" Range("D2") = "<=99" Range("A7:C30000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("A1:D2"), Unique:=False Range("C6").Copy Sheets("MonthStats").Select Range("D3").Select Selection.PasteSpecial Paste:=xlPasteValues Sheets("FileFolDB").Select Range("A2") = "NSW" Range("B2") = "Subscriber" Range("C2") = "=1" Range("D2") = "<=99" Range("A7:C30000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("A1:D2"), Unique:=False Range("C6").Copy Sheets("MonthStats").Select Range("E3").Select... Selection.PasteSpecial Paste:=xlPasteValues etc............ |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code to replace Advanced Filter
Bob,
Since sending my other reply, I've figured out (a bit) where the range comes from to paste the values. It's the last bit of the FilterIt "NSW", "Member", "=1", "<=99", "D3" line. As well as the other problem I mentioned in my other post, could you also explain then, how the D3 in this line becomes the target? As you can tell, I don't really understand too much, but I'd like to as I need to add the rest of the lines. Rob "Bob Phillips" wrote in message ... Does this help? Sub advfilter() FilterIt "NSW", "Member", "=1", "<=99", "D3" FilterIt "NSW", "Subscriber", "=1", "<=99", "E3" 'etc. End Sub Private Sub FilterIt(crit1, crit2, crit3, crit4, target) With Sheets("FileFolDB") .Range("A2").Value = crit1 .Range("B2").Value = crit2 .Range("C2").Value = crit3 .Range("D2").Value = crit4 .Range("A7:C30000").AdvancedFilter Action:=xlFilterInPlace, _ CriteriaRange:=.Range("A1:D2"), _ Unique:=False .Range("C6").Copy Sheets("MonthStats").Range(target) End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "RobN" wrote in message ... I am applying advanced filter to determine different numbers from my data so I can paste the result to a different cell in another sheet. I am using the following code (only 2 parts shown), to filter the data (using advanced filter) and this process has to occur many times (132) with changes to the filters (cells A2 to D2) and the cells where the results need to go. (In the code, Range C6 holds a Subtotal formula that counts the data in that column). I was wondering if there is a way to get my 132 different amounts without having to go through this process and instead use some code to obtain the various results and paste those results into the appropriate places in the MonthStats sheet. The FileFolDB data changes each month so the process needs to be repeated each month. Rob Sheets("FileFolDB").Select Range("A2") = "NSW" Range("B2") = "Member" Range("C2") = "=1" Range("D2") = "<=99" Range("A7:C30000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("A1:D2"), Unique:=False Range("C6").Copy Sheets("MonthStats").Select Range("D3").Select Selection.PasteSpecial Paste:=xlPasteValues Sheets("FileFolDB").Select Range("A2") = "NSW" Range("B2") = "Subscriber" Range("C2") = "=1" Range("D2") = "<=99" Range("A7:C30000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("A1:D2"), Unique:=False Range("C6").Copy Sheets("MonthStats").Select Range("E3").Select... Selection.PasteSpecial Paste:=xlPasteValues etc............ |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code to replace Advanced Filter
Bob,
Back again. I completely entered all that was required so that your code would produce the required 132 values (albeit incorrect values due to some error as explained previously). I find that this code takes about 2 +1/4 mins to finish whereas the code I started with only takes 1 +1/2 mins to run. It seems a bit odd as they both basically do the same thing. The speed comparison is between the following multiplied by 132: Sub ExtractMonthStats() Sheets("FileFolDB").Select Range("A2") = "NSW" Range("B2") = "Member" Range("C2") = "=1" Range("D2") = "<=99" Range("A7:C30000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("A1:D2"), Unique:=False Range("C6").Copy Sheets("MonthStats").Select Range("D3").Select Selection.PasteSpecial Paste:=xlPasteValues End Sub and...... Sub advfilter() FilterIt "NSW", "Member", "=1", "<=99", "D3" End Sub Private Sub FilterIt(crit1, crit2, crit3, crit4, target) With Sheets("FileFolDB") .Range("A2").Value = crit1 .Range("B2").Value = crit2 .Range("C2").Value = crit3 .Range("D2").Value = crit4 .Range("A7:C30000").AdvancedFilter Action:=xlFilterInPlace, _ CriteriaRange:=.Range("A1:D2"), _ Unique:=False .Range("C6").Copy Sheets("MonthStats").Range(target) End With End Sub I was hoping to have a procedure that would reduce the time. Anyway, thanks for trying. Rob "Bob Phillips" wrote in message ... Does this help? Sub advfilter() FilterIt "NSW", "Member", "=1", "<=99", "D3" FilterIt "NSW", "Subscriber", "=1", "<=99", "E3" 'etc. End Sub Private Sub FilterIt(crit1, crit2, crit3, crit4, target) With Sheets("FileFolDB") .Range("A2").Value = crit1 .Range("B2").Value = crit2 .Range("C2").Value = crit3 .Range("D2").Value = crit4 .Range("A7:C30000").AdvancedFilter Action:=xlFilterInPlace, _ CriteriaRange:=.Range("A1:D2"), _ Unique:=False .Range("C6").Copy Sheets("MonthStats").Range(target) End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "RobN" wrote in message ... I am applying advanced filter to determine different numbers from my data so I can paste the result to a different cell in another sheet. I am using the following code (only 2 parts shown), to filter the data (using advanced filter) and this process has to occur many times (132) with changes to the filters (cells A2 to D2) and the cells where the results need to go. (In the code, Range C6 holds a Subtotal formula that counts the data in that column). I was wondering if there is a way to get my 132 different amounts without having to go through this process and instead use some code to obtain the various results and paste those results into the appropriate places in the MonthStats sheet. The FileFolDB data changes each month so the process needs to be repeated each month. Rob Sheets("FileFolDB").Select Range("A2") = "NSW" Range("B2") = "Member" Range("C2") = "=1" Range("D2") = "<=99" Range("A7:C30000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("A1:D2"), Unique:=False Range("C6").Copy Sheets("MonthStats").Select Range("D3").Select Selection.PasteSpecial Paste:=xlPasteValues Sheets("FileFolDB").Select Range("A2") = "NSW" Range("B2") = "Subscriber" Range("C2") = "=1" Range("D2") = "<=99" Range("A7:C30000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("A1:D2"), Unique:=False Range("C6").Copy Sheets("MonthStats").Select Range("E3").Select... Selection.PasteSpecial Paste:=xlPasteValues etc............ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced Filter - filter rows < | Excel Discussion (Misc queries) | |||
Why won't advanced filter return filter results? | Excel Worksheet Functions | |||
How do I use advanced filter to filter for blank cells? | Excel Discussion (Misc queries) | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions | |||
advanced filter won't allow me to filter on bracketed text (-456.2 | Excel Discussion (Misc queries) |