Running macro in one workbook that refers to variable in another

[Andyjim]

There are 2 macros below:
The first one defines a user's workbook name (we will not know that) and
jockeys back and forth with a workbook in which we do know the name. THe
problem arises when the first macro calls the second macro. THe second macro
resides in the known named workbook and it needs to jockey back and forth
with the 1st workbook. The problem is the second macro does not recognize
the variable name that represents the first workbook. I know this sounds
confusing. I hope the code below explains some of the problem:
Real question is, How can we refer to the 1st worksheet when we won't know
the name. We do paste its name into a cell in the 2nd file. Can we somehow
use that cell data to refer to the 1st workbook?

Thanks so much.


Sub RunUpdate()

Dim updfile As String
Dim usrfile As Workbook
Dim backname As String
Dim p As String
p = ActiveWorkbook.Path
updfile = p & "\" & "fxRM_Update.xls"
Set usrfile = ActiveWorkbook

backname = p & "\" & "Backup" & usrfile.Name
currentuserfile = p & "\" & usrfile.Name



'Insert Filename in Filename cell
Sheets("Lookup").Select


usrfile.Activate

Sheets("Lookup").Select
Application.GoTo Reference:="Filename"
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("Lookup").Select
Application.GoTo Reference:="UserFilename"
ActiveSheet.Paste

'Copy Current Version to Update file, Old Version cell
usrfile.Activate
Sheets("Lookup").Select
Application.GoTo Reference:="CurrentVersion"
Application.CutCopyMode = False
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("Lookup").Select
Application.GoTo Reference:="OldVersion"
ActiveSheet.Paste
Application.Run ("fxRM_Update.xls!update3") ' CALLS MACRO FROM
KNOWN-NAMED FILE--fxRM_Update.xls
'End Sub



MACRO 2
sub Update3 ()
usrfile.Activate ' THIS VARIABE NOT RECOGNIZED
With Worksheets("lookup")
Range("A40").Select
ActiveCell.FormulaR1C1 = "BOO!"

End With
Windows("fxRM_Update.xls").Activate

With Worksheets("lookup")
Range("A40").Select
ActiveCell.FormulaR1C1 = "BOOHOO!"
End With

End Sub

 

[Leith Ross]

There are 2 macros below:
The first one defines a user's workbook name (we will not know that) and
jockeys back and forth with a workbook in which we do know the name. THe
problem arises when the first macro calls the second macro. THe second macro
resides in the known named workbook and it needs to jockey back and forth
with the 1st workbook. The problem is the second macro does not recognize
the variable name that represents the first workbook. I know this sounds
confusing. I hope the code below explains some of the problem:
Real question is, How can we refer to the 1st worksheet when we won't know
the name. We do paste its name into a cell in the 2nd file. Can we somehow
use that cell data to refer to the 1st workbook?

Thanks so much.

Sub RunUpdate()

Dim updfile As String
Dim usrfile As Workbook
Dim backname As String
Dim p As String
p = ActiveWorkbook.Path
updfile = p & "\" & "fxRM_Update.xls"
Set usrfile = ActiveWorkbook

backname = p & "\" & "Backup" & usrfile.Name
currentuserfile = p & "\" & usrfile.Name

'Insert Filename in Filename cell
Sheets("Lookup").Select

usrfile.Activate

Sheets("Lookup").Select
Application.GoTo Reference:="Filename"
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("Lookup").Select
Application.GoTo Reference:="UserFilename"
ActiveSheet.Paste

'Copy Current Version to Update file, Old Version cell
usrfile.Activate
Sheets("Lookup").Select
Application.GoTo Reference:="CurrentVersion"
Application.CutCopyMode = False
Selection.Copy
Windows("fxRM_Update.xls").Activate
Sheets("Lookup").Select
Application.GoTo Reference:="OldVersion"
ActiveSheet.Paste
Application.Run ("fxRM_Update.xls!update3") ' CALLS MACRO FROM
KNOWN-NAMED FILE--fxRM_Update.xls
'End Sub

MACRO 2
sub Update3 ()
usrfile.Activate ' THIS VARIABE NOT RECOGNIZED
With Worksheets("lookup")
Range("A40").Select
ActiveCell.FormulaR1C1 = "BOO!"

End With
Windows("fxRM_Update.xls").Activate

With Worksheets("lookup")
Range("A40").Select
ActiveCell.FormulaR1C1 = "BOOHOO!"
End With

End Sub

Hello Andyjim,

Your variable "usrfile" goes out scope between macro calls. You need
to declare "usrfile" in the same VBA Module as your macros like this:

Declare userfile As Workbook

This will make the variable available to all procedures in all modules
in your project as long as the VBA project is running.

Sincerely,
Leith Ross

 

[Andyjim]

I must be missing something, Leith.

I placed in the second workbook module this declaration:
Public usrfile As Workbook (in the same module as UPdate3). However, when I
run the macro from the 1st workbook I get:

Object variable or with block variable not set.

I don't know how to use the SET statement in the second module, because I
don't know the name of the 1st workbook from which I initiated the 1st macro.

I also tried placing that same Public usrfile as workbook in the 1st
workbook, but that didnt seem to help.

Any help would be greatly appreciated.

Thanks

Andy

 

[Leith Ross]

I must be missing something, Leith.

I placed in the second workbook module this declaration:
Public usrfile As Workbook (in the same module as UPdate3). However, when I
run the macro from the 1st workbook I get:

Object variable or with block variable not set.

I don't know how to use the SET statement in the second module, because I
don't know the name of the 1st workbook from which I initiated the 1st macro.

I also tried placing that same Public usrfile as workbook in the 1st
workbook, but that didnt seem to help.

Any help would be greatly appreciated.

Thanks

Andy

Hello Andy,

If you like, I can take a look at your workbooks if you can send them
to me. It would make correcting the code easier. My email is
(e-mail address removed).

Sincerely,
Leith Ross