Autofilter more than 1 worksheet

[Gazza]

I have a workbook that contains 4 worksheets, all with lots of data on them.
All have autofilter set up and what I am hoping to achieve is to set the
criteria on 1 worksheet in the first column (say in cell B1) that will then
become the default criteria on all the other worksheets.

I found the following code through google but can't get it to work - I
assume the first macro can be pasted into a module but I'm not sure what to
do with the second bit.

OR is there an easier way

Thanks in advance for any help

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

 

[OssieMac]

This macro works. Use mid function in lieu of right function to remove = sign
from beginning of criteria because allowing the default for number of
characters to select includes all characters from 2nd one so the length of
the criteria does not matter. Note: I have always found that the sheet needs
to be activated before the filter can be set in the macro.

Your first macro is designed to autorun with an event but from your query
you don't seem too confident in that area so I have simply used one you can
paste into a module which you can run from Tools, macro etc or you can put in
a command button.

Dim InitFilter 'Initial Filter Setting
Dim InitSheet 'Name of Initial Sheet

Sub Multi_Sht_Auto_Filt()
InitFilter = Mid(ActiveSheet.AutoFilter.Filters(1).Criteria1, 2)
InitSheet = ActiveSheet.Name
For Each sht In Worksheets
If sht.Name <> InitSheet Then 'No need reset initial sheet
sht.Select
Selection.AutoFilter Field:=1, Criteria1:=InitFilter
End If
Next
End Sub

 

[Gazza]

Thanks OssieMac

This works well enough for what I want and as you suggest I will assign the
macro to a command button.

Leads on to a couple of things though (still getting to grips with this VBA
stuff)

1 The macro crashes if there is a sheet in the workbook that does not have
the autofilter set up (doesn't have any data that I need filtering but I can
remove it for now. So I need to find a method that will trap this error or
presumably I can pass the sheet names to the proceedure somehow.

2 As you say I'm not too confident either about event triggering procedures
either so that's something else to put on my TODO list.

It's very helpful though having people like yourself who take the time and
trouble to help others out with this stuff.

best regards

Gazza

 

[OssieMac]

No problems doing that. I don't mind helping people get started. However if
the answer is helpful then don't forget to answer yes to the question "Was
this post helpful" at the bottom of the screen. Select my last post and do
the same.

Sub Multi_Sht_Auto_Filt()
InitFilter = Mid(ActiveSheet.AutoFilter.Filters(1).Criteria1, 2)
InitSheet = ActiveSheet.Name
For Each sht In Worksheets
If sht.Name <> InitSheet Then 'No need reset initial sheet
sht.Select
If ActiveSheet.AutoFilterMode Then 'Confirms Autofilter on the
sheet.
Selection.AutoFilter Field:=1, Criteria1:=InitFilter
End If
End If
Next
End Sub

And if you want to make it better still, the following line of code could be
inserted at the beginning of your code so that it will confirm if the
AutoFilter selection has been made. (ie. Not set to All) before running the
macro. You could make it an If Then Else with a msgbox in the Else to tell
the user to set the filter before running the macro.

If ActiveSheet.FilterMode Then 'Confirms that selection has been made.

 

[Tom Ogilvy]

No problems doing that. I don't mind helping people get started. However

if
the answer is helpful then don't forget to answer yes to the question "Was
this post helpful" at the bottom of the screen. Select my last post and do
the same.

Since the OP is not posting from the microsoft communities, he probably has
no idea what you are talking about.