Select specific sheet based on cell value with loo

Question

I am very new with programming and I am trying to finish a small Project for my Company. I am trying to write a code that loops through a range and for every cell.value greater than 0 it will find corresponding excel sheet and execute the specific code. Thank you!

Sub test()

Dim rng As Range, cell As Range

Set rng = Range("B3:B53")

For Each cell In rng
    If cell > 0 Then
        SheetName = ThisWorkbook.Sheets(cell.Value)
        ThisWorkbook.Sheets(SheetName).Select
        ActiveWindow.SelectedSheets.PrintOut Copies:=1
        Range("E4:P50").Select
        Selection.ClearContest
    End If
Next cell

End Sub

Answer 1

Try:

Sub test()

Dim rng As Range, Cell As Range
Dim ws As Worksheet

Set rng = Sheets(1).Range("B3:B53")

On Error Resume Next
For Each Cell In rng
    If Cell.Value > 0 Then
        Set ws = Sheets(Cell.Value)
        If Not ws Is Nothing Then
            With ws
                .PrintOut Copies:=1
                .Range("E4:P50").ClearContents
            End With
        End If
    End If
Next Cell
End Sub

Answer 2

Hopefully this is what you are looking for.. A simple code

Dim cell            As Range
Dim cell2           As Range
Dim cell3           As Range

    Set cell = Cells.Find(What:="Your Value", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

    Set cell2 = Cells.Find(What:="Your Value", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

    Set cell3 = Cells.Find(What:="Your Value", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

If cell Is Nothing Then
    'Your code
Else
    'Your code
End If

If cell2 Is Nothing Then
    'Your code
Else
    'Your code
End If

If cell3 Is Nothing Then
    'Your code
Else
    'Your code
End If

You can add more cell values by setting its variables.

Please let us know if you have any query..

Answer 3

Try

If cell > 0 Then
    dim ws as worksheet
    set ws = ThisWorkbook.Sheets(cell.Value)
    ws.PrintOut Copies:=1
    ws.Range("E4:P50").ClearContest
End If