CODEWITHSUNDEEP
excelvba
phalanx
phalanx

Reputation: 497

How to activate an excel sheet that belongs to a different Workbook

Let's take this code as example(inside an excel VBA macro):

             --more code--
        Sheets("Sheet2").Activate
        ActiveSheet.Range("A1").Select  
           --more code--

The above code lines switches to sheet2 (a sheet inside the Workbook I'm using). But what about if I want to open a sheet of another Workbook?

I've tried things like this:

Sheets("C:\MYROUTE\[MyWorkbook.xlsx]Sheet2").Activate

or

Sheets("C:\MYROUTE\MyWorkbook.xlsx!Sheet2").Activate

But it doesn't work Any ideas to write it properly?

Upvotes: 0

Views: 1560

Answers (2)

artis_meditari
artis_meditari

Reputation: 68

Apply workbook to sheet:

my_file.Sheets("Sheet2").Range("A1").Select  
current_wb.Sheets("Sheet1").Range("B2").Select

Upvotes: 2

ApplePie
ApplePie

Reputation: 8942

You can select the workbook using the following code and then use worksheets as you would normally in your active workbook.

Dim my_path as String, my_file as String, current_wb as String
my_path = "C:\whatever..."
my_file = "whatever.xlsx"
current_wb = ActiveWorkbook.Name

Workbooks.Open my_path & "­\" & my_file 'This is to pen the workbook
Workbooks(my_file).Activate            'This is to activate the recently opened workbook
Workbooks(current_wb).Activate         'This is to activate the workbook that was first open

Upvotes: 1

Related Questions