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)
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,277
Is MySpreadsheet.xlsx opened in the same instance of Excel that contains the code?
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
One way is to first select that other workbook, then just use:
Set book2 = ActiveWorkbook
 

chrissnead

New Member
Joined
Apr 10, 2018
Messages
16
No the code I'm creating is actually an add-in. The active workbook is not MySpreadsheet.xlsx
 

chrissnead

New Member
Joined
Apr 10, 2018
Messages
16

ADVERTISEMENT

I already tried doing this and I'm getting the same error.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
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.
 

chrissnead

New Member
Joined
Apr 10, 2018
Messages
16

ADVERTISEMENT

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?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
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?
 

chrissnead

New Member
Joined
Apr 10, 2018
Messages
16
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,109,005
Messages
5,526,234
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top