is there a way to?

[SteveDB1]

Hi all.
Is there a way to filter two worksheets simultaneously?

I've recorded the following autofilter:

Sub FilterC()
ActiveSheet.Range("$A$3:$J$29").AutoFilter Field:=6, Criteria1:="<>"
Sheets("Sum").Select
ActiveSheet.Range("$A$8:$F$13").AutoFilter Field:=1, Criteria1:="<>"
End Sub

It's not generic enough to use for all instances of my desired goal. And
none of the books we have discuss this topic.

I.e., I want to select a filter criteria on one worksheet, and have it set
the identical filter on the other worksheet.

Thank you.

 

[paul.robinson]

Hi
Search this Newsgroup using "find autofilter setting". You should see
a post by Tom Ogilvy which shows you how to find the current setting
on a sheet.
regards
Paul

 

[Don Guillett]

Your fields didn't agree but I think this answers your question.

Sub Filterbasedonsheet1()
myfield = Sheets("sheet1").Range("e1")
mc = Sheets("sheet1").Range("g1")
ma = Array("Sheet1", "Sheet2")
For Each c In ma
If Sheets(c).AutoFilterMode Then Sheets(c).AutoFilterMode = False
Sheets(c).Range("A1:C100").AutoFilter Field:=myfield, Criteria1:=mc
Next c
End Sub

 

[SteveDB1]

Don, thank you for the response.

The fields don't match because they are two different worksheets. The first
field-- activeworksheet is on sheet1. The second field is on Sheet2-- in this
specific case- the worksheet named "Sum."

As I've studied this more closely I found that it's selecting the Field
value based on the contents of the cell in the range chosen for myfield. If
there's nothing in the cell it throws an error.
This is not what I want.
There needs to be two distinct fields, and one single criteria.


i.e.,

FilterA.autofilter Field:= 6, Criteria1 := myCrit, Criteria2:= MyCrit1

FilterB.autofilter Field:=1,Criteria1 :=myCrit, Criteria2:= MyCrit1

Also, my need for a second criteria will vary.

Somehow I need to set the filter on each page simultaneously to match those.
which was why I had two distinct fields in my recorded filter code.

I hope that's clear. If not, pelase let me know.

 

[SteveDB1]

Hi Paul,
I'm guessing you're referring to the discussion between robs3131 and Dave P?
It was the only one I could find with anything close to that title.
I typed in Tom's name as a search parameter, and your title, the only
response I received back was this post thread.

Do you have a link?
Thank you.

 

[Don Guillett]

If desired, send your workbook to my address below along with snippets of
these messages and clear examples and explanations of what you want. Doable.

 

[SteveDB1]

Thank you.
It'll be along shortly.

If desired, send your workbook to my address below along with snippets of
these messages and clear examples and explanations of what you want. Doable.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)