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

rdormond

New Member
Joined
Jun 1, 2015
Messages
11
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?


Code:
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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.
Code:
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.
Code:
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,179
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top