How to set another workbook as a variable

chrissnead

New Member
Joined
Apr 10, 2018
Messages
16
I am trying to set an open workbook as variable book2 but keep getting a 'Subscript out of range' error. Below is the code I am working with. It errors at 'Set book2'. It's completely random and will work sometimes but most of the time it doesn't. Could someone help?

Set book1 = ActiveWorkbook
Set book2 = Workbooks("MySpreadsheet.xlsx") ' errors out here


Set lookFor = book1.Sheets(1).Cells(NextRow, 2) ' value to find
Set srchRange = book2.Sheets(1).Range("A:N") 'source


lookFor.Offset(0, 6).Value = Application.VLookup(lookFor, srchRange, 11, False)
lookFor.Offset(0, 7).Value = Application.VLookup(lookFor, srchRange, 10, False)
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Is MySpreadsheet.xlsx opened in the same instance of Excel that contains the code?
 
Last edited:
Upvote 0
One way is to first select that other workbook, then just use:
Set book2 = ActiveWorkbook
 
Upvote 0
I already tried doing this and I'm getting the same error.
On which line, exactly?
The selecting of the other workbook, or the setting of the workbook variable.
Note that both workbooks must be in the same Excel session. Depending on how you are opening them, they could be in different Excel sessions.
 
Upvote 0
Right after setting book1 = ActiveWorkbook I tried activating the other workbook then doing a Set book2 = ActiveWorkbook.

What do you mean by in the same Excel session? They are both "Read Only" aka they aren't saved to the desktop. Does that have anything to do with it?
 
Upvote 0
What is your exact error message?
If you hit debug, which line of code does it highlight in yellow?
That is the line we want to focus on.

If it is highlighting the Activate line for the other workbook, it means you cannot activate it, which means that most likely:
- You are activating it incorrectly
- You have a typo in the file name
- It is in a different Excel session

How exactly are you opening these Excel files?
One way to tell if they are both in the same session is to look at the Project Explorer in the VB Editor. It will list all your open Workbooks in that session. Do you see both of them listed there?
 
Upvote 0
Highlights Set book2 = Workbooks("MySpreadsheet.xlsx")

I'm opening them from an Outlook email. I am seeing both workbooks in the same session as well.
 
Upvote 0
Set book2 = Workbooks("MySpreadsheet.xlsx")
I thought you said you were activating this other workbook and using:
Code:
Set book2 = ActiveWorkbook
like I said up in post 3.

Please post your current code in its entirety, so we can see exactly what you are doing.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,155
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