Looping Through Sheets

Posted by Mark Watford on January 24, 2002 2:34 PM

I am trying to write code to loop through the sheets in a workbook and check a cell for a particular value.
I am having a problem with the code. Could someone give me an example so I could get started. I understand the principle for looping through cells within a sheet, but can't quite get the code for loopin through sheets in a workbook.

Thanks

Posted by Russell Hauf on January 24, 2002 3:17 PM

If you are just looping through to find cells on a worksheet, you can use something like the code below. If you want to loop through all sheets including chart sheets, etc., then it's just a hair different.

Dim wks as Worksheet

For Each wks In ActiveWorkbook.Worksheets
If wks.Range("A1") > 10 Then
' Do something
End If
Next wks

Hope this helps,

Russell

Posted by Mark Watford on January 25, 2002 5:39 AM

That helped me very much Thanks Again. One more question, in my If statement once it meets the criteria I am looking for I need the name of the Worksheet to the left of the worksheet it is now looking at. For example:
If wks.Range("A1")=100 Then
Sheets("RECAPO").Range("C100")=wks.Name
This gives me the name of the sheet that is presently being looked at. I really need the name of the sheet at wks-1.How would you code that.

Thanks

Posted by Barrie Davidson on January 25, 2002 6:24 AM

You could use something like this:

i=1
For Each wks In ActiveWorkbook.Worksheets
If wks.Range("A1") > 10 Then
If wks.Range("A1")=100 Then
Sheets("RECAPO").Range("C100")=Sheets(i-1).Name
End If
End If
i=i+1
Next wks

Note, you will get an error if the first worksheet has 100 in A1 (because "i-1" will equal zero).

Hope this helps you out.
Barrie
Barrie Davidson