Switching between Workbooks within two different macros

rammi125

New Member
Joined
Jun 3, 2015
Messages
22
I have a macro that opens a few spreadsheets which pull in data from Bloomberg which takes a few seconds. After the formulas are updated I have a second macro that I want to paste over the data as values.

However when I try to activate the name of the workbook in the second macro I get the following error: Run-time error ‘424’; Object required.

Code from first macro which is on ABC workbook:

Set XYZBook = ActiveWorkbook

(random code here)

XYZBook.Activate


(wait for formulas to update)

2nd macro also on ABC workbook first tries to do XYZBook.activate and fails.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

scott_n_phnx

Active Member
Joined
Sep 28, 2006
Messages
445
Instead of using ActiveWorkbook, use ThisWorkbook. ThisWorkbook works with the workbook where the code resides. What I usually do is something like:

Code:
Dim wbHome as Workbook
Dim wbTarget as Workbook

Set wbHome = ThisWorkbook
Set wbTarget = Workbooks.Open("myworkbook.xlsx")
 

rammi125

New Member
Joined
Jun 3, 2015
Messages
22
Instead of using ActiveWorkbook, use ThisWorkbook. ThisWorkbook works with the workbook where the code resides. What I usually do is something like:

Code:
Dim wbHome as Workbook
Dim wbTarget as Workbook

Set wbHome = ThisWorkbook
Set wbTarget = Workbooks.Open("myworkbook.xlsx")

Thanks Scott for your response! I tried a few different variations of the above and still can't get it to work. In the first macro when I open the workbook I used the above code. Three questions:

1 - Does ("myworkbook.xlsx) have to include the directory where the file is saved? I tried that and get an error: Method 'Range' of object'_Global failed
2 - the name of the file changes everyday (includes today's date), how would I address that
3 - Most importantly, in the second macro when I tried wbTarget.Activate it didn't work?


THANKS AGAIN for your help!
 

scott_n_phnx

Active Member
Joined
Sep 28, 2006
Messages
445
To answer your questions

1 - Yes, it needs to include the full file path. For one of mine, my file is located on a Sharepoint site and so I use <b>Set wbTarget = Workbooks.Open("https://teamsites2013.mydomain.com/sites/Working Documents/ToolsReport.xlsx")</b>.

2 - This snippet will allow you to select a file to use as wbTarget
Code:
fn = Application.GetOpenFilename
Set wbTarget = Workbooks.Open(fn)

3 - The second macro, does that one reside in the wbTarget workbook, or in the original workbook?
 

Forum statistics

Threads
1,147,747
Messages
5,742,966
Members
423,769
Latest member
LongToast

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
Top