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............