Filter applied to cell not working

[jh9940]

Hi

Joe4 kindly solved a problem I had whereby you can click a particular cell on 5 identical sheets e.g., E4 and it would apply a filter (VBA - multiple filters from clicking different cells). I have copy and pasted the identical macro code onto each sheet. However, I've run into a problem with one of the macro filters.

I have 6 sheets - Year 7, Year 8, Year 9, Year 10 and Year 11 (all of which are identical) and a 'Home'. On the 'Home' sheet are 5 boxes with "Year 7", "Year 8" etc..., which open up the corresponding sheets. On each of the sheets is a button called 'Home' which users can click to go back to 'Home' - i.e., essentially allowing them to close all sheets and start again to look at another year group.

This is the code that I currently use under ThisWorkbook:

Sub Workbook_Open()
Show_Home_Only
ProtectAndAllowFiltering
End Sub

Sub Show_Home_Only()
Dim curSheet As Object
For Each curSheet In Sheets
If curSheet.Name <> "Home" Then
curSheet.Visible = False
End If
Next curSheet
End Sub

Sub ProtectAndAllowFiltering()
Dim wSheetName As Worksheet
For Each wSheetName In Worksheets
wSheetName.Protect Password:="school", UserInterFaceOnly:=True, AllowFiltering:=True, AllowSorting:=True
Next wSheetName
End Sub

When I use Joe4's solution (i.e., for each sheet I've copied the identical code to apply a macro to a particular cell) for the macro 'Show_Home_only' on each sheet I run into an error and it won't run. In other words, it won't allow me to apply that macro to a particular cell - I think it has something to do with the 'Show_Home_only' macro being on ThisWorkbook?

This is probably as clear as mud!

Huge thanks for your help

 

[RoryA]

Only Workbook_Open should be in the ThisWorkbook module. The other two should be in a normal module.