Finding worksheet out of open workbooks

[ks1987]

Hi All,

I am trying to write a code which accomplishes the following:

1. Amongst all open workbook activate workbook with 10th sheet named as Run
2. Display message if no such sheet found in any workbook

I have written the following code for the first point:

Sub IdentifyValidationTool()
Dim w As Workbook

For Each w In Workbooks
If w.Worksheets(10).Name = "Run" Then
w.Activate
End If
Next w
End Sub

I am struggling to figure out the logic and the code for the second part. Any help on the same is much appreciated

 

[VoG]

Perhaps

Sub IdentifyValidationTool()
Dim w As Workbook
Dim Found As Boolean
For Each w In Workbooks
    If w.Worksheets(10).Name = "Run" Then
        Found = True
        w.Activate
        Exit For
    End If
Next w
If Not Found Then MsgBox "Not found"
End Sub

 

[ks1987]

Perhaps

Sub IdentifyValidationTool()
Dim w As Workbook
Dim Found As Boolean
For Each w In Workbooks
    If w.Worksheets(10).Name = "Run" Then
        Found = True
        w.Activate
        Exit For
    End If
Next w
If Not Found Then MsgBox "Not found"
End Sub

Hi VoG,

While testing the first If condition, I encounter the subscript out of range error in case of workbooks where the 10th sheet does not exist. How can I take care of this?

Thanks,
ks1987

 

[VoG]

Maybe

Sub IdentifyValidationTool()
Dim w As Workbook
Dim Found As Boolean
For Each w In Workbooks
    If w.Worksheets.Count >= 10 Then
        If w.Worksheets(10).Name = "Run" Then
            Found = True
            w.Activate
            Exit For
        End If
    End If
Next w
If Not Found Then MsgBox "Not found"
End Sub

 

[ks1987]

Maybe

Sub IdentifyValidationTool()
Dim w As Workbook
Dim Found As Boolean
For Each w In Workbooks
    If w.Worksheets.Count >= 10 Then
        If w.Worksheets(10).Name = "Run" Then
            Found = True
            w.Activate
            Exit For
        End If
    End If
Next w
If Not Found Then MsgBox "Not found"
End Sub

Works like a charm. Great use of Boolean. I look forward to reading up more on it.