How to properly use Activate or Select method with multiple workbooks VBA

[rdormond]

Hey party people,

I am trying to navigate and read data from specific sheets from multiple workbooks using VBA.

When working with a single workbook, I use SheetX.Select often (X = 1, 2, 3... whatever sheet number I'm looking for at the moment) because the tabs change names, move order, or become hidden as the workbook becomes more refined and this method seems to keep my code from breaking, so far.

I also noticed when working with a single workbook with hidden sheets... Sheet13.Select DOES NOT always function equally to Worksheets(13).Activate in terms of activating the proper sheet. It seems the order in which the sheets are placed determines which sheet is opened if you use Activate method, and it will not open strictly based on its Sheet#, could someone expand on this and either confirm or correct my thoughts?

When working with two (or multiple) workbooks, I've only been able to navigate to the secondary workbook and its sheets by using the .Activate method, example is below... But my code is not opening the correct sheet as the observation above as stated, how do I properly extend the quasi-dynamic referencing to activate by sheet# instead of sheet name in the secondary workbook?

Dim wkb1, wkb2 As Workbook
Set wkb1 = ThisWorkbook

Workbooks.Open Filename :=MyFolder & "\" & MyFile
Set wkb2 = Workbooks(MyFile)  
'This workbook contains hidden tabs, that I would like to remain hidden

wkb2.Worksheets(13).Activate   
'opens incorrect sheet. I want Sheet13... this opens Sheet16 which is a tab to the left of Sheet13. 

'wkb2.Worksheet(21).Activate    'opens Sheet13... but I assume I need to update this if I hide/unhide or add/delete sheets
'wkb2.Sheets13.Activate   Run-time error '438': Object doesn't support this property or method
'wkb2.Sheet13.Select   Run-time error '438': Object doesn't support this property or method
'wkb2.Worksheets(13).Select   Run-time error '1004': Select method of Worksheet class failed

'Then read and do code on active sheet

 

[Norie]

You should avoid using Activate/Select.

Is Sheet13 the codename of the workbook you want to work with in the workbook you are opening?

If it is you could use a function like this to get a reference to it.

Function GetShByCodeName(strShCodeName, Optional wb As Workbook) As Worksheet
Dim ws As Worksheet

    If IsMissing(wb) Then
        Set wb = ThisWorkbook
    End If
    
    For Each ws In wb.Sheets
        If ws.CodeName = strShCodeName Then
            Set GetShByCodeName = ws
            Exit For
        End If
    Next ws
    
End Function

In your code it would look something like this.

Dim wkb1 As Workbook, wkb2 As Workbook
Dim wsSrc As Worksheet

    Set wkb1 = ThisWorkbook

    Set wkb2 = Workbooks.Open (Filename :=MyFolder & "\" & MyFile)

    Set wsSrc = GetShByCodeName("Sheet13, wkb2)

    ' check we found sheet
    If wsSrc Is Nothing Then
        MsgBox "Worksheet not found!"
        Exit Sub
    End If

    'Then read and do code on wsSrc

 

[rdormond]

Hey Norie,

Thanks for the quick response. Yes, Sheet13 is the code name of the sheet that I want to open.

I'll implement the changes and see how it goes!

 

[rdormond]

Norie,

The function worked wonderfully, thank you!