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.
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,918
Office Version
365
Platform
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,198
Messages
5,509,770
Members
408,752
Latest member
KrisF

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top