New to VBA and need some help

[mcgrathchris]

I am trying to make my life a little easier! I have a workbook that always has a variable amount of tabs (one for each person) as we add or remove people. Also have the first sheet as a "Recap" sheet. Need help with the code for the following (everything I have tried gets me errors):

Clear contents of "Recap" sheet Range ("AB4:AB33") then clear ALL remaining other sheets Ranges ("J9:M15, A47:j78").

Can this be done without entering each sheet name in the code? Tried "Next ws" but with no luck.

Appreciate the help.
Chris

 

[Smitty]

Welcome to the Board!

See if this does what you need:

<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>        Sheets("Recap").Range("AB4:AB33").ClearContents<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:#00007F">If</SPAN> ws.Name <> "Recap" <SPAN style="color:#00007F">Then</SPAN><br>                ws.Range("J9:M15, A48:J78").ClearContents<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> ws<br>        <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

HTH,

 

[mcgrathchris]

Awesome. That did it. Huge time saver!!!!!!!!!!!!!!!