Cannot share originating workbook name as variable across workbooks

[richardpfister]

I know how to declare public variables and then reference them from other workbooks but I am unable to make this work when the originating workbook name is the variable. Here is my scenario:

User opens Workbook1 and runs Macro1, which does the following

Public wb1 As Workbook
Public wb2 As Workbook
Sub Macro1()
Set wb1 = ActiveWorkbook

Workbooks.Open ("Workbook2.xlsm")
Set wb2 = ActiveWorkbook

Application.Run wb2.Name & "!Macro2"
End Sub

Macro2 (in wb2) is called and runs fine until I reference wb1

Sub Macro2()
wb1.Activate  <code breaks="" here,="" runtime="" error="" 424:="" object="" required="">''it throws a 424 object required error here

It throws an error because wb1 is empty. I want to late-bind it (e.g. Application.Workbooks("Workbook1.xls").variablename) but that requires the very details I'm trying to store in a variable.

Someone's going to ask so here's my end goal: I have 20 workbooks used by multiple users. I want to maintain one master file of macros accessible from the 20. I first tried having the 20 files replace their own VBProjects with those in the master file; that was messy and unsuccessful. So now I'm trying to have the 20 files open the master workbook and run the macros from there. So there's a kick off program to open the master and then run the macros in it. The problem I'm having is that the 20 files must all be named differently, so I don't know how to refer back to the originating file once the master is open and running.

Hopefully this all makes sense and somebody has some ideas. Thanks very much in advance for your time.

- Richard</code>

 

[Warship]

First add a Name in Workbook2 called "CallingBook" and set its value to "" (empty string)

Sub Macro1()
    Set wb1 = ActiveWorkbook
    Workbooks.Open ThisWorkbook.Path & "/Workbook2.xlsm"
    Set wb2 = ActiveWorkbook
    wb2.Names("CallingBook").RefersTo = ThisWorkbook.Name
    Application.Run wb2.Name & "!Macro2"
End Sub

Sub Macro2()
    Dim CallingBook As String
    CallingBook = Evaluate("CallingBook")
    Windows(CallingBook).Activate
End Sub

 

[richardpfister]

Hi Warship,
Thanks for your reply. I got a runtime error 1004 @ line "wb2.Names("CallingBook").RefersTo = ThisWorkbook.Name" when trying to run this. So I tweaked it a little and it worked just fine, thanks!!

(modified line):

wb2.Names.Add Name:="CallingBook", RefersTo:=ThisWorkbook.Name

 

[Warship]

First add a Name in Workbook2 called "CallingBook" and set its value to "" (empty string)

Your are welcome.

The 1004 was likely due to the Name not existing in wb2.

I had forgotten that "wb2.Names.Add" simply updates when the Name DOES exist.
So that code should work going forward even after the Name is already there.
Guess I pulled a Homer on that one... DOH!

All the better though. Glad it worked out for you!