Running macro on all worksheets in a book

[F6Hawk]

I have a macro that I would like to run on every worksheet in my book (over 100 sheets wide). As it takes about 20 secs to run on each sheet, and I don't want to click from sheet to sheet, how can I make it run on every sheet in the book? And no, they aren't named sequentially, either alphabetically or numerically.

Also, is there a function in Excel to return the number of sheets in a book?

Thanks!
F6Hawk

[F6Hawk]

Never mind on the counting sheets part, I found that via search. Sorry!

[protonLeah]

You may be able to wrap your macro code in something like this:

Public Sub test()
     Dim wb As Object
     Dim sh
     Set wb = Workbooks("book1")
     For Each sh In wb.Sheets
          MsgBox sh.Name
     Next sh
End Sub 

[shg]

protonLeah's code will do just what you want. I would modify it to declare object variables by type for better performance, and so that Intellisense works:

Please Login or Register  to view this content.

[F6Hawk]

Well, I tried that (at least, I thought I did, with my code below), but all it did was run the code over & over on the first sheet.

Please Login or Register  to view this content.

[shg]

You can either modify your macro to reference the correct sheet (ws.Range(...), or do

Please Login or Register  to view this content.

before calling your macro.

Edit: BTW, that code will error if you have chart sheets.

[broro183]

hi F6Hawk,

Do you have any code in place to try & minimise the time of 20 seconds per sheet (eg "application.screenupdating = false" &/or "Application.Calculation = xlCalculationManual")?

VbNoob recently provided the following link to Charles Williams' below link which has some good suggestions for optimising speed

Please Login or Register  to view this content.

in the below thread

http://excelforum.com/showthread.php?t=617075


To overcome the possibility of errors mentioned by SHG you can change "For Each wS In sheets" to "For Each wS In Worksheets" as this ignores Chart sheets.

hth
Rob

[F6Hawk]

The problem is that I have 186 sheets, and calling them by name will be a pain in the...

I guess I am missing something here, becaue thus far, all these do is run my macro repeatedly on the SAME sheet, it does not select subsequent sheets to run the macro on.

You can either modify your macro to reference the correct sheet (ws.Range(...), or do

Please Login or Register  to view this content.

before calling your macro.

Edit: BTW, that code will error if you have chart sheets.

[F6Hawk]

Ok, I think I got it now...

Please Login or Register  to view this content.

This one runs my RunAll macro on a sheet, selects the next sheet, runs it, and so on.

Thanks for all the help, folks!

F6

[broro183]

Thanks for the feedback, I'm pleased we could help :-)

Just out of curiosity, does it still take 20sec per sheet to run?

Rob