How to activate the next active Workbook with multiple spreadsheets open

j4ttlife

New Member
Joined
Feb 2, 2022
Messages
30
Platform
  1. Windows
  2. MacOS
Hi All,

My macro uses two workbooks. The first workbook is my main workbook (ThisWorkbook) where the macro runs, and this macro opens another workbook that I use after.

My code is:
VBA Code:
Dim new_WB as Workbook

Set new_WB = Workbooks(2)

new_WB.Activate

Currently I can only run this macro by having no other workbooks open as it locates the second open workbook.

If I have multiple spreadsheets open before running this macro it will pull in data from the incorrect spreadsheet.

Is there a way I can set new_WB to be equal to the next open workbook rather than the second workbook so it locates the correct spreadsheet regardless of how many workbooks I have open?

Thanks.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
I use a FileDialog variable to prompt open File Explorer that allows the user to open the desired workbook.

VBA Code:
Dim fd as FileDialog

Set fd = Application.fileDialog(msoFileDialogOpen)

With fd
    .Filters.Clear
    .Title = "Select file to open"
    .AllowMultiSelect = False
    .Show

    .InitialFileName = "\\xxxx\xxxxx\xxxxx" 'file path to a certain folder so users access the right folder in the file explorer prompt instantly

End with
 
Upvote 0
That does not show where you open the workbook.
 
Upvote 0
That does not show where you open the workbook.
VBA Code:
Dim fd as FileDialog

Set fd = Application.fileDialog(msoFileDialogOpen)

With fd
    .Filters.Clear
    .Title = "Select file to open"
    .AllowMultiSelect = False
    .Show

    .InitialFileName = "\\xxxx\xxxxx\xxxxx" 'file path to a certain folder so users access the right folder in the file explorer prompt instantly
    .Execute


End with

The code above opens the file, and then I use the code below to activate it and make the changes:

VBA Code:
Dim new_WB as Workbook

Set new_WB = Workbooks(2)

new_WB.Activate

This works fine as long as my original spreadsheet where the macro runs is the only spreadsheet open.

If I have multiple spreadsheets open prior to running this macro, it will identify Workbooks(2) as the second workbook that I have open.
 
Upvote 0
Ok, try it like
VBA Code:
Dim fd As FileDialog
Dim new_WB As Workbook

Set fd = Application.FileDialog(msoFileDialogOpen)

With fd
    .Filters.Clear
    .Title = "Select file to open"
    .AllowMultiSelect = False

    .InitialFileName = "\\xxxx\xxxxx\xxxxx" 'file path to a certain folder so users access the right folder in the file explorer prompt instantly
   If .Show Then Set new_WB = Workbooks.Open(.selecteditems(1))

End With
 
Upvote 0
Solution
Ok, try it like
VBA Code:
Dim fd As FileDialog
Dim new_WB As Workbook

Set fd = Application.FileDialog(msoFileDialogOpen)

With fd
    .Filters.Clear
    .Title = "Select file to open"
    .AllowMultiSelect = False

    .InitialFileName = "\\xxxx\xxxxx\xxxxx" 'file path to a certain folder so users access the right folder in the file explorer prompt instantly
   If .Show Then Set new_WB = Workbooks.Open(.selecteditems(1))

End With
Ignore my previous replies if you can still see them, I'm unsure how to delete them. This has worked perfectly. Thank you again for the help!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

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