Need a second Myname value for second workbook

[Bill_Biggs]

I have a macro has to move between two workbooks. In the first workbook when the macro is activted, I have set up the workbook know its name with the following:

Myname = ThisWorkbook.Name
Q = Myname

When the second macro is opened, I need the macro to remember the second workbooks name. Initially, I have it open the workbook with the following:

Filename = Application.GetOpenFilename("XLS Files(*.xls),", , "Select Latest Received File")
If Filename = False Then
MsgBox "No file was selected"
Exit Sub
End If

A = Filename
Application.ScreenUpdating = True
Workbooks.Open Filename:=A

But now I need the macro to create a DIM with only the workbook name as Q is set up above. I tried assigning a second myname value (D=myname2) here but it simply defaults to the first myname value, thuw not repoening the swecond book when commanded. How do I get the macro to remember and recall the second workbook name without closing and reopening it?

Thanks,

Bill

 

[Norie]

Bill

Why not make life a little easier and create a reference to the workbook you open?

Set wbOpen = Workbooks.Open (Filename:=A)

By the way what is the code actually doing that you need to switch between workbooks?

PS Oh, and I would recommend you use something a little more descriptive for your variable names.

 

[Smitty]

When workbook 2 is opens it then becomes the activeworkbook, so you can refer to it with MyName2 = ActiveWorkbook.Name.

Also note that you should be able to condense your code a bit:

            If Filename = False Then
                MsgBox "No file was selected"
                Exit Sub
            Else: Workbooks.Open Filename
            End If

HTH,

 

[Bill_Biggs]

Thank you both!