Macro to goto the next availible

[Microsoft Communities]

I need a macro that will start at one sheet and then go to the next sheet
that has B1 as a blank cell.
I have several sheets before this one and want it to check each sheet
beginning with sheet "01-08-09" until it reaches sheet "31-08-09"
Can anyone help with this?

 

[Dave Peterson]

And after it finds that empty cell, just stop there and select it?

If yes:

Option Explicit
Sub testme()

Dim dCtr As Date
Dim StartDate As Date
Dim TestWks As Worksheet
Dim DateStr As String
Dim FoundIt As Boolean

StartDate = DateSerial(2009, 8, 1)

FoundIt = False
For dCtr = DateSerial(Year(StartDate), Month(StartDate), 1) _
To DateSerial(Year(StartDate), Month(StartDate) + 1, 0)

Set TestWks = Nothing
On Error Resume Next
DateStr = Format(dCtr, "dd-mm-yy")
Set TestWks = Worksheets(DateStr)
On Error GoTo 0

If TestWks Is Nothing Then
'comment this line if you don't want to know
MsgBox "There isn't a worksheet named: " & DateStr
Else
If IsEmpty(TestWks.Range("B1").Value) Then
FoundIt = True
Application.Goto reference:=TestWks.Range("B1"), scroll:=True
Exit For
End If
End If
Next dCtr

If FoundIt = False Then
MsgBox "Not found!"
End If

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Don Guillett]

Sub doselectedsheets()
On Error Resume Next
For i = 1 To 13
mv = Format(i, "00") & "0809"
With Sheets(mv)
'MsgBox .Range("b1")
..Select
If Len(Application.Trim(.Range("b1"))) < 1 Then Exit For
End With
Next i
End Sub