Applying a macro to all worksheets
 

I have a macro that contains the line:

With Worksheets("UK")

Can I get it to apply to all my worksheets?

Ie

With Worksheets(All)

Thanks!

Applying a macro to all worksheets
 

Use something like With Worksheets(Activesheet)

this will work with any worksheet.

There is no worksheets(All) as all the worksheets is really the
workbook collection

DannyS wrote:
I have a macro that contains the line:

With Worksheets("UK")

Can I get it to apply to all my worksheets?

Ie

With Worksheets(All)

Thanks!

Applying a macro to all worksheets
 

maybe.......

With Activesheet

Vaya con Dios,
Chuck, CABGx3



"DannyS" wrote:

I have a macro that contains the line:

With Worksheets("UK")

Can I get it to apply to all my worksheets?

Ie

With Worksheets(All)

Thanks!

Applying a macro to all worksheets
 

Great stuff, thanks again to you two guys!

"PMC1" wrote:

Use something like With Worksheets(Activesheet)

this will work with any worksheet.

There is no worksheets(All) as all the worksheets is really the
workbook collection

DannyS wrote:
I have a macro that contains the line:

With Worksheets("UK")

Can I get it to apply to all my worksheets?

Ie

With Worksheets(All)

Thanks!

Applying a macro to all worksheets
 

Ah, hang on. When I use Activesheets I get a runtime error. My macro looks
like this:

Sub auto_open()
With Worksheets(ActiveSheet)
.Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
'.EnableAutoFilter = True
End With
End Sub

"PMC1" wrote:

Use something like With Worksheets(Activesheet)

this will work with any worksheet.

There is no worksheets(All) as all the worksheets is really the
workbook collection

DannyS wrote:
I have a macro that contains the line:

With Worksheets("UK")

Can I get it to apply to all my worksheets?

Ie

With Worksheets(All)

Thanks!

Applying a macro to all worksheets
 

maybe.......

With Activesheet

Vaya con Dios,
Chuck, CABGx3





"DannyS" wrote:

Ah, hang on. When I use Activesheets I get a runtime error. My macro looks
like this:

Sub auto_open()
With Worksheets(ActiveSheet)
.Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
'.EnableAutoFilter = True
End With
End Sub

"PMC1" wrote:

Use something like With Worksheets(Activesheet)

this will work with any worksheet.

There is no worksheets(All) as all the worksheets is really the
workbook collection

DannyS wrote:
I have a macro that contains the line:

With Worksheets("UK")

Can I get it to apply to all my worksheets?

Ie

With Worksheets(All)

Thanks!

Applying a macro to all worksheets
 

In the same way that you can not select multipe sheets and protect them
manually, you can not have VBA protect multiple sheets at once. What you can
do is have VBA cycle through all of the sheets and process them one at a time
something like this...

Sub auto_open()
dim wks as worksheet

for each wks in worksheets
with wks
.Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
'.EnableAutoFilter = True
End With
next wks
End Sub

--
HTH...

Jim Thomlinson


"DannyS" wrote:

Ah, hang on. When I use Activesheets I get a runtime error. My macro looks
like this:

Sub auto_open()
With Worksheets(ActiveSheet)
.Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
'.EnableAutoFilter = True
End With
End Sub

"PMC1" wrote:

Use something like With Worksheets(Activesheet)

this will work with any worksheet.

There is no worksheets(All) as all the worksheets is really the
workbook collection

DannyS wrote:
I have a macro that contains the line:

With Worksheets("UK")

Can I get it to apply to all my worksheets?

Ie

With Worksheets(All)

Thanks!

Applying a macro to all worksheets
 

Yes!

Great stuff - thanks Jim!


"Jim Thomlinson" wrote:

In the same way that you can not select multipe sheets and protect them
manually, you can not have VBA protect multiple sheets at once. What you can
do is have VBA cycle through all of the sheets and process them one at a time
something like this...

Sub auto_open()
dim wks as worksheet

for each wks in worksheets
with wks
.Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
'.EnableAutoFilter = True
End With
next wks
End Sub

--
HTH...

Jim Thomlinson


"DannyS" wrote:

Ah, hang on. When I use Activesheets I get a runtime error. My macro looks
like this:

Sub auto_open()
With Worksheets(ActiveSheet)
.Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
'.EnableAutoFilter = True
End With
End Sub

"PMC1" wrote:

Use something like With Worksheets(Activesheet)

this will work with any worksheet.

There is no worksheets(All) as all the worksheets is really the
workbook collection

DannyS wrote:
I have a macro that contains the line:

With Worksheets("UK")

Can I get it to apply to all my worksheets?

Ie

With Worksheets(All)

Thanks!