EXCEL VBA - Filter multiple sheets at the same time

[cutestuff]

hi,

I was told this was possible, but I've googled and searched and haven't exactly found anything.

We have multiple sheets in one excel file. There is one column that is the same on all the sheets. What we want is when we filter that column for a particular item, we want all the other sheets to filter for that item too. That way they can go from one sheet to another and see the information for the one they filtered for.

Hope this makes sense. Hope someone out here can help.

Thank you so much in advance.
Appreciate it.

 

[SkipVought]

Hi,

There is no event that corresponds to an AutoFilter selection. However, if your have your Calculate set to Automatic AND you have a formula to calculate on the sheet, when you select an AutoFilter criteria, the Worksheet_Calculate event will fire. In that event, you can capture the Selected Criteria from one or more AutoFilter filters...

Private Sub Worksheet_Calculate()
    MsgBox ActiveSheet.AutoFilter.Filters(1).Criteria1
End Sub

Having tht criteria, it can then be applied to any other filter in any other sheet.

set wsThis = Activesheet
for each ws in worksheets
  if ws.name <> wsThis.name then
     ws.autofilter.filter(1).criteria = right(wsthis.autofilter.filter(1).criteria,len(wsthis.autofilter.filter(1).criteria)-1)
  end if
next

Skip,

_[red][/red]

 

[cutestuff]

hi Skip,

Thanks for the help!
However, I am getting an "Object doesn't support property or method" error.

Any clue why?

Thanks so much again!

 

[SkipVought]

On what statement? Use the Debug button.

Skip,

_[red][/red]

 

[cutestuff]

hi Skip,

On this statement:

ws.autofilter.filter(1).criteria = right(wsthis.autofilter.filter(1).criteria,len(wsthis.autofilter.filter(1).criteria)-1)

Thanks!

 

[SkipVought]

Do you have the AutoFilter on on all sheets?

In the debugger, right click on ws and edit in the window ws.name Is there an AutoFilter on this tab?

Also this is looking at the FIRST filter. IS that what you want?

Skip,

_[red][/red]