Loopy Worksheet Problem

[G-fer]

Hi ...

I have a routine that runs continuously and needs to loop through half a dozen worksheets, perform some arithmetic, and then wait for a while before starting again.

I am able to employ Application.ScreenUpdating = False to prevent the display changing while the routine is running, but focus goes to the last sheet altered by the routine when the loop completes.

I would like to return the focus to the sheet that I was viewing when the loop starts, rather than having to reselect it.

How do I assign a variable xxx to the name of the worksheet that was displayed when the loop starts so that I can return to that sheet using something like Worksheets(xxx).Select when the loop finishes?

 

[G-fer]

Mmmm ...

How can I offset from a cell without selecting the cell???

G.

 

[GTO]

You can offset from the referenced cell or range. For example, let's say we have a workbook with six sheets (named Sheet1 thru Sheet6).

Presume the data on ea sheet is this:

Excel Workbook
	A	B	C
1	3	4	5
2	Yes	No	Yes
Sheet1

Excel 2010



Then this would loop thru the sheets, skipping the 2 we don't want, and loop thru the range, checking the offset of ea cell.<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> exa2()<br><SPAN style="color:#00007F">Dim</SPAN> wks<SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">Dim</SPAN> rCell<SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> lVal<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">For</SPAN><SPAN style="color:#00007F">Each</SPAN> wks<SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Worksheets<br>        <SPAN style="color:#00007F">If</SPAN><SPAN style="color:#00007F">Not</SPAN> wks.Name = "Sheet2" _<br>        And<SPAN style="color:#00007F">Not</SPAN> wks.Name = "Sheet3"<SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">For</SPAN><SPAN style="color:#00007F">Each</SPAN> rCell<SPAN style="color:#00007F">In</SPAN> wks.Range("A1:C1")<br>                <SPAN style="color:#00007F">If</SPAN> rCell.Offset(1).Value = "Yes"<SPAN style="color:#00007F">Then</SPAN> lVal = lVal + rCell.Value<br>            <SPAN style="color:#00007F">Next</SPAN><br>        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN><br>    <br>    MsgBox "lVal = " & lVal<br><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Mark