is there a way to?

S

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

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
 
D

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
 
S

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

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

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

SteveDB1

Thank you.
It'll be along shortly.

Don Guillett said:
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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top