referencing multiple worksheets in macro

[Michelle Thompson]

So I kind of found the answer to my question in a post from a couple years
ago but am still confused so if anyone could help I'd appreciate it...I'm
trying to protect the sheet while still being able to use the subtotal
function. I have several worksheets I want to do this for and this is the
post from before with the code but I can't figure out where to put the names
of my worksheets ("01","02","03") and what I need to customize in the code
for my file. Any suggestions?

Option Explicit
Sub auto_open()
dim wks as worksheet
for each wks in thisworkbook.worksheets
with wks
.select 'see note below
.Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
.EnableAutoFilter = True
End With
application.goto thisworkbook.worksheets(1).range("a1"), scroll:=true
End Sub

Tom Ogilvy has reported that sometimes protecting sheets will work better if
it's selected first.

Change the application.goto line to where you want to goto <vbg> when the
code
ends.

 

[JLatham]

First, your code appears to be missing a "Next" to go with the "For each
wks..." statement.

But, in that code, wks represents each worksheet in the workbook in turn.
It doesn't need to know the sheet's name to do what it is doing.

But if you need to determine a worksheet's name within that loop, you can
use something like
If wks.Name = "01" Then
'do something special with sheet named 01
End If

What the code is doing is this, for each and every sheet in the workbook:

..Select ' selects/activates a sheet
..Protect ... ' that turns the protection on for the selected sheet
..EnableOutlining = True ' does that for the selected sheet
..EnableAutoFilter = True ' does that for the selected sheet

Once it has worked through all of the worksheets, it is going to jump back
to the first sheet in the workbook and select cell A1 on it.


The code with the missing Next statement added

Option Explicit
Sub auto_open()
dim wks as worksheet
for each wks in thisworkbook.worksheets
with wks
.select 'see note below
.Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
.EnableAutoFilter = True
End With
Next
application.goto thisworkbook.worksheets(1).range("a1"), scroll:=true
End Sub

 

[Michelle Thompson]

thank you very much for explaining the code and fixing it for me, it worked
great!