Code for multiple worksheets

[Paul Sheppard]

I am using excel 2000

How can I make the following code (saved in the This Workbook sheet)available to all or multiple sheets in my workbook

Private Sub Workbook_Open()
'check for filter, turn on if none exists
With Worksheets("Missing Data")
If Not .AutoFilterMode Then
.Range("A3").AutoFilter
End If
.EnableAutoFilter = True
.Protect _
Contents:=True, UserInterfaceOnly:=True
End With
End Sub

[royUK]

That's in the WorkBook module, which bit do you want to make aplicable to all sheets?

[Paul Sheppard]

That's in the WorkBook module, which bit do you want to make aplicable to all sheets?

This code only works on the worksheet called Missing Data, how can I make it work on other worksheets in the same workbook

[royUK]

Try this

Please Login or Register  to view this content.

[Paul Sheppard]

Try this

Please Login or Register  to view this content.

I have tried this and in the above code .AutoFilterMode is highlighted, with the message Compile Error: Invalid or unqualified reference

[royUK]

try this

Please Login or Register  to view this content.

[Paul Sheppard]

try this

Please Login or Register  to view this content.

I have tried this and in the above code .EnableAutoFilter = is highlighted, with the message Compile Error: Invalid or unqualified reference

[royUK]

Try this

Please Login or Register  to view this content.

[Paul Sheppard]

Try this

Please Login or Register  to view this content.

This just seems to loop through all the sheets in the workbook and make sure they are protected, but does not allow me to filter on any sheets

[SOS]

Hi Paul,

Is there any data in your sheets?

When I tried roy's code on an EMPTY workbook it did as you said and just looped and protected the sheets but when I put some data (but only in Cells A2, A3, A4 or B4) on any sheet it turned on the autofilter. If data were put in ANY OTHER cell it did not turn on the autofilter.

[Paul Sheppard]

Hi Paul,

Is there any data in your sheets?

When I tried roy's code on an EMPTY workbook it did as you said and just looped and protected the sheets but when I put some data (but only in Cells A2, A3, A4 or B4) on any sheet it turned on the autofilter. If data were put in ANY OTHER cell it did not turn on the autofilter.

Hi SOS

I have data down to row 82

Row 1 is a Header Row
Row 2 is Blank
Row 3 contains Column Headings
Rows 4-81 contain my data

and

Row 82 Totals

[royUK]

The code sets the filter to Row 3, you need an empty row between the header Row & Totals. Add a row & change A3 to A4, see if that works

[Paul Sheppard]

The code sets the filter to Row 3, you need an empty row between the header Row & Totals. Add a row & change A3 to A4, see if that works

Hi ROYUK

Have added a blank row and changed A3 to A4, if I save file with filters off and sheets unprotected the next time I open the file the filters appear and work, if I then save the file with the filters on , the next time I open the file the filters do not work

I need the sheets to be protected all the time as the file is sent to various people and they should not have access to make changes

[royUK]

It doesn't do that for me

[Paul Sheppard]

Hi ROYUK

Thanks for the file, but it doesn't work for me

Would it be easier if we went back to my original code, as it worked for one worksheet called Missing Data

Please Login or Register  to view this content.

and just added specific worksheets, the one I need is called Commission

[royUK]

Do you want to filter all sheets or just the one?

[Paul Sheppard]

2 sheets "Missing Data" and "Commission"

[royUK]

You could use the Workbook_SheetActivate event. This will check for a Filter whenever you activate either sheet.

Please Login or Register  to view this content.

[Paul Sheppard]

Hi ROYUK

Have tried that but still cannot get ti to work.

I have saved your file and opened it and that does not work either

[royUK]

It works for me, are you enabling macros?

[Paul Sheppard]

It works for me, are you enabling macros?

Yes

Could it be to do with add ins? I only have Analysis Toolpak, Analysis Toolpak - VBA and Solver Add-In selected?

[royUK]

I have added a couple of mesageboxes that say what the macro is doing.

Addins should not affect this code.

[Paul Sheppard]

I have added a couple of mesageboxes that say what the macro is doing.

Addins should not affect this code.

Saved the file, it opened on the Commission worksheet, the filter did not work, so clicked on the Missing Data worksheet and got a message box with the message "Filter on"

[royUK]

The code works when you activate the sheet, so go back to the other sheet & it should work. If you want it to work on opening then move the code to WorkBook_Open

[Paul Sheppard]

The code works when you activate the sheet, so go back to the other sheet & it should work. If you want it to work on opening then move the code to WorkBook_Open

I opened on the file it opened with Commission worksheet, the filter did not work, so clicked on the Missing Data worksheet and got a message box with the message "Filter on", clicked OK filter still did not work, so clicked on the "Missing Data" worksheet, the message box with the message "Filter on" appeared, clicked OK filter still did not work

I would like it to work on opening the workbook, but do not know what you mean by

move the code to WorkBook_Open

Sorry not v good at VB

[royUK]

the macro runs when the workbook is opened

[Paul Sheppard]

the macro runs when the workbook is opened

Hi RoyUK

Thanks for all your help and patience with this. The last file you posted still did not work. Have now managed to solve the problem with the following code

Please Login or Register  to view this content.

Once again much appreciate your help and patience

Paul