Retrieve a worksheet based on the worksheet name

 2011-07-21    Worksheets    0    132

Both of the function examples below can be very useful when you create a solution that is depending on input from a special worksheet. Instead of bothering the user by asking about the worksheet and workbook that contains the data source, you can use the functions below to find that special worksheet, as long as it always has the same "special" name. The function below will return a worksheet object from one workbook if it finds a worksheet with a name that matches the one you are looking for:

Function GetWorksheetByName(wb As Workbook, strWorksheetName As String) As Worksheet
Dim strWS As String, w As Long
    If wb Is Nothing Then Exit Function
    If Len(strWorksheetName) = 0 Then Exit Function
    
    Application.StatusBar = "Looking for worksheet named """ & strWorksheetName & """ in " & wb.Name & "..."
    strWS = LCase(strWorksheetName)
    With wb
        For w = 1 To .Worksheets.Count
            If LCase(.Worksheets(w).Name) = strWS Then
                Set GetWorksheetByName = .Worksheets(w)
                w = .Worksheets.Count ' exit loop
            End If
        Next w
    End With
    Application.StatusBar = False
End Function

Sub ExampleGetWorksheetByName()
Dim ws As Worksheet
    Set ws = GetWorksheetByName(ActiveWorkbook, "SpecialWorksheetName")
    If ws Is Nothing Then Exit Sub ' worksheet not found

    Debug.Print "Found Worksheet in " & ws.Parent.Name & ": " & ws.Name
    Set ws = Nothing
End Sub
The function below uses the function above and will return a worksheet object from all open workbooks if it finds a worksheet with a name that matches the one you are looking for:
Function GetWorksheetByNameAllWB(strWorksheetName As String) As Worksheet
Dim wb As Workbook
    If Len(strWorksheetName) = 0 Then Exit Function
    
    For Each wb In Application.Workbooks
        Set GetWorksheetByNameAllWB = GetWorksheetByName(wb, strWorksheetName)
        If Not GetWorksheetByNameAllWB Is Nothing Then
            Exit For
        End If
    Next wb
    Set wb = Nothing
    Application.StatusBar = False
End Function

Sub ExampleGetWorksheetByNameAllWB()
Dim ws As Worksheet
    Set ws = GetWorksheetByNameAllWB("SpecialWorksheetName")
    If ws Is Nothing Then Exit Sub ' worksheet not found

    Debug.Print "Found Worksheet in " & ws.Parent.Name & ": " & ws.Name
    Set ws = Nothing
End Sub