How can I get a worksheet object from a specific workbook (not 'ThisWorkbook') using the worksheets CodeName?

Question

I want to obtain a worksheet object from a specific workbook, like so:

 Set ws = Application.Workbooks("WorkBookName.xlsm").Worksheets("sheet1")

However, I want to use the CodeName of the worksheet since the sheet's name can be changed. For example:

 Set ws = Application.Workbooks("WorkBookName.xlsm").Sheet1

Now, I know this doesn't work since Sheet1 is a global variable create by the VBA editor for convenience.

I also know I could use the index (i.e. Worksheets(1)) to obtain the worksheet object, but if someone moves the worksheets in the workbook around, functions will not work properly.

So my question is: How can I get a worksheet object from a specific workbook (not 'ThisWorkbook') and store it in a variable using the CodeName?

Here is a sample snippet (I'm very new to posting in stackoverflow.com):


Public Sub Main()

    ' Test Class
    Dim test As New StoreContestModel
    test.StoreID = "28"
    test.StoreName = "Kippersville"

    ' Other workbook
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim r As Range
    Dim v As String

    ' Showing test StoreID in other workbook. Code is broken up because as one-line it did not work.
    Set wb = Application.Workbooks("Contest.xlsm")
    Set ws = Application.Workbooks("Contest.xlsm").Sheet1 ' wb.Sheet1 didn't work
    Set r = ws.Range("N2")
    r.value = test.StoreID

End Sub

I want the StoreID to show up in the cell specified in the other workbook. However, I get this error:

Run-time error '438':

Object doesn't support this property or method

Tim Williams Tim Williams · Accepted Answer · 2019-06-25T21:57:13

You can do something like this:

Sub Tester()

    Dim ws As Worksheet

    Set ws = SheetFromCodeName(ActiveWorkbook, "Sheet1")

    Debug.Print ws.Name

End Sub

Function SheetFromCodeName(wb As Workbook, cn As String)
    Dim s, rv As Worksheet
    For Each s In wb.Worksheets
        If s.CodeName = cn Then
            Set rv = s
            Exit For
        End If
    Next s
    Set SheetFromCodeName = rv
End Function

How can I get a worksheet object from a specific workbook (not 'ThisWorkbook') using the worksheets CodeName?

1 Answers