only run macro on worksheet if value in cell A2 then on next worksheet in range

ronjo

New Member
Joined
Jul 14, 2010
Messages
17
I have a macro that works fine. I wish to run it on 12 worksheets one after the other but only if cell A2 of the active worksheet has data in otherwise go to next worksheet in the range and run the macro. In other words go to the first sheet in the array of sheets, test cell A2 and if blank skip to next sheet. If A2 has value run the macro then go to next sheet and so on until all 12 sheets have been processed. The reason is the macro crashes if it hits an empty sheet and cell A2 is always going to have a value if the sheet has data. There are other sheets in the workbook so I need to be able to specify the array of sheets somehow. Any help will be gratefully accepted.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,232
Office Version
  1. 365
Platform
  1. Windows
I think something like this should be what you want.

<font face=Courier New><br><br><SPAN style="color:#00007F">Sub</SPAN> Testing()<br>    <SPAN style="color:#00007F">Dim</SPAN> wsList<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    wsList = Array("Sheet1", "Sheet2", "Rearrange") <SPAN style="color:#007F00">'<-- List of sheets to check here</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> i = 0 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(wsList)<br>        <SPAN style="color:#00007F">With</SPAN> Sheets(wsList(i))<br>            <SPAN style="color:#00007F">If</SPAN> .Range("A2").Value <> "" <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#007F00">'Rest of your code goes here</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> i<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 

ronjo

New Member
Joined
Jul 14, 2010
Messages
17
Thanks very much Peter. It did not do exactly what was wanted but pointed me in the right direction. I wanted the result of the macro on the sheet that it was calculating but the results appeared in the "G" column of whatever sheet was active at the time of running the code. Thanks again for taking the trouble to answer my query. Regards
 

Watch MrExcel Video

Forum statistics

Threads
1,133,718
Messages
5,660,482
Members
418,580
Latest member
IrishDave2137

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top