Applying a macro to a range of sheets

[Allan Walker]

Hi!

So, I've managed to get my macro for my report cleaning done, and I'm really happy! Thanks for all the help from the good contributors at Mr Excel!

But I have a little problem - the code is very close to the 64K limit, and therefore I can't just copy paste and append the code.

So, I think I need to do a loop, but I'm still novice.

My range of worksheets are:

"Report Week 1" "Report Week 2" "Report Week 3" and "Report Week 4".

My macro is called "Report Cleaner".

At the moment I am manually selecting each worksheet in turn, and then running the macro. At the end of each one a msgbox pops up to say done. Ideally, what would happen is that the macro runs once on each sheet in the range. I can always take the msgbox out, but it would be nice to add it at the end...

Thanks once again in advance,

Allan

 

[Arul.rajesh]

dim sht as worhsheet
For each sht in activeworkbook.worksheets

'Your code here

Next sht

TO loop through all sheets on a workbook

 

[VoG]

Try like this

For Each ws In Worksheets(Array("Report Week 1", "Report Week 2", "Report Week 3", "Report Week 4"))
    ws.Select
    '
    'rest of code
    '
Next ws
'
'msgbox here

 

[obiron]

I am assuming that in your macro you refer to the sheet being cleaned as

Activesheet

If so then put the following code round your macro

Dim ws as Worksheet
For Each ws of Worksheets
ws.Activate
....
do your macro stuff here
...
 
Next ws

If you don't want it to run for all worksheets then you could also put in some validation based on the worksheet name.


Alternatively, you could modify the macro so that is accepts a worksheet name as an argument

sub Report_Cleaner(SheetToClean as string)
 
worksheets(SheetToClean).activate
.....
 
do your macro stuff
....
End Sub

and then create a new macro that simply calls the old one for each sheet

Sub master_cleaner()
 
Call Report_Cleaner("Report week 1")
Call Report_Cleaner("Report week 2")
Call Report_Cleaner("Report week 3")
Call Report_Cleaner("Report week 4")
...
End Sub

 

[Allan Walker]

As always, magnificent.

Thanks all!

Allan