selectin sheets based on value of cell

[BrendanDixon]

Hi all,

I have a worksheet that has a sheet called input and then sheets called 1, 2, 3, 4,5 all the way up to 30

I am using a macro to select all the sheets 1 to 30 but would like to make it more intelligent in that it will only select the sheets based on whether a cell has a value or is empty.

Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30")).Select

Basically I want if Cell C9 has a value it will select sheet "1", D9 will select sheet "2" all the way to AF9 will select sheet "30"

 

[VoG]

Try

Sub test()
Dim i As Long, Got As Boolean
For i = 1 To 30
    If Sheets(CStr(i)).Range("C9").Value <> "" And Sheets(CStr(i)).Range("C9").Value <> 0 Then
        If Not Got Then
            Sheets(CStr(i)).Select
            Got = True
        Else
            Sheets(CStr(i)).Select Replace:=False
        End If
    End If
Next i
If Not Got Then
    MsgBox "No data"
    Exit Sub
End If
End Sub

 

[BrendanDixon]

PERFECT!!!!!!!


Thanks so much VoG I have been wanting to do this for such along time.


Thanks!!!