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

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

 

[BrendanDixon]

Ok I sort of have an idea. but the code is only selecting the first sheet that has the cell filled in

If Sheets("Input").Range("C9").Value <> "" Then
    Sheets("1").Select
ElseIf Sheets("Input").Range("D9").Value <> "" Then
    Sheets("2").Select
End If

how can I make is select multiple sheets

 

[BrendanDixon]

Hi VoG that code does not seem to do anything

 

[VoG]

Sorry, try

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

 

[BrendanDixon]

Oh wait a minute I see what you are trying to do
I think this will work. let me test

 

[BrendanDixon]

Ok this worked perfectly in my test sheet I made up

but now in my actual workbook the cell C9 on each sheet has a function ie sheet 1 C9 =Setup!B2, sheet 2 =Setup!B3

So what is happening is that it is selecting every sheet

 

[AlphaFrog]

Not to step on VoG's usually excellent replies, I think this is what you want...

Sub test()

    Dim i As Long, Got As Boolean
    
    For i = 1 To 30
        If Sheets("Input").Range("B9").Offset(, i).Value <> "" Then
            If Not Got Then
                Sheets(CStr(i)).Select
                Got = True
            Else
                Sheets(CStr(i)).Select Replace:=False
            End If
        End If
    Next i
    
End Sub

It checks if cells in Sheets("Input").Range("C9:AB9") are empty and selects sheets based on that. Or maybe I have it wrong???

 

[VoG]

Or maybe

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
End Sub

 

[BrendanDixon]

Hi AlphaFrog,

You are correct in what you were saying but I think VoG,s Idea will also work where checks each of the sheets for a value in a specific cell.

I see it is working now. Just one small request now I would like to add error checking where if none of these sheets are selected then it will end the sub, as I have other code after this that it carries on with

Maybe something like

if no sheets selected then
MSG No sheets are filled in
End Sub
EndIf

I will have sheet:Input selected but I do not want that as part of the selection