On Jan 19, 2:20 pm, Andyjim wrote:
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