*** How do you have a macro automatically repeat on all worksheets in a workbook and stop after the last one, then return to the first sheet? Thank yo

[brad7989]

So I have a macro that formats a bunch of data. Currently I run it, then press CTRL + Page Down to shift sheets, then run it again, then shift sheets, run it, etc.

I want to fix it to make it automatically run through all sheets, then return to the first sheet afterwards.

Thank you so much for your help!

 

[VoG]

Try posting your code.

 

[brad7989]

It's several printed pages long of basic formatting functions. I have it format an entire worksheet, then just return to selecting cell A1 (it's all contained to one worksheet as is).

It works the same on each worksheet... I figure that I can just run it and have the last command switch pages, then loop to the beginning of the whole macro and run again, then loop after switching a second time, etc. I just 1) don't know how to do the loops and 2) don't know how to make it stop (besides erroring out) after it finishes all work sheets.

Here's an exerpt from my code:

Application.Goto Reference:="R2000C14"
Selection.End(xlUp).Select
ActiveCell.Range("A1:B1").Select
Selection.Style = "Currency"
Selection.NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
Selection.NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
Application.Goto Reference:="R1C1"

 

[Smitty]

There's generally no need to select sheets in order to work with them, so if you do it right, you'll never leave the first sheet at all.

Here's how you can work on all sheets:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br>        <br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets<br>            <SPAN style="color:#007F00">'   Your code</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> ws<br>        <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[brad7989]

Smitty,

First off - thanks for your help!

I ran what you said, but it started redoing the code again for the first sheet and errored out.

One function in the code is to search for something specific to delete... It had already been deleted and couldn't be found, so it errored and stopped before doing anything to the next page.

Where should I go from here?

 

[Smitty]

If your code is erroring on Find you can usually just use On Error Resume Next for that particular one (it's not great practice to ignore errors instead of trap them specifically, but in this case that's what happens when Find can't).

 

[brad7989]

Smitty,

How do I do that?

 

[brad7989]

Also, is it a problem that the code is even doing find again on the same worksheet? That means it's running the code twice on same sheet and not switching sheets...

 

[Smitty]

How do I do that?

On Error Resume Next
' Find code here

Also, is it a problem that the code is even doing find again on the same worksheet? That means it's running the code twice on same sheet and not switching sheets...

Are you looking for two different things? What happens when the first is found? What about the second?

 

[brad7989]

I'm just looking for one thing. When it is found, it's deleted.

What's weird is that when the code loops again, it is searching the first worksheet I ran it on instead of the next worksheet... and it can't find it there, because it was deleted.

I'm not sure why it is looking there instead of on the next sheet.

Thanks for your help!