Switch to unknown named workbook

[Djblois]

I am writing a macro and in it I have a box requesting the name of the workbook. Later in the macro it switches to a different workbook and then I have to switch back to the newly named workbook. I know how to switch a workbook that I know the name of but not one that is named based on the input of the user. Please help me.

Daniel

[lucas]

Hi Daniel,
I'm having a little trouble trying to understand what your trying to do. Could you post what you have so far or the code leading up to the box requesting the name of the workbook....is it a messagebox or a form, etc.

[Djblois]

Lucas it is a message box, not a form. I haven't gotten into forms yet. I will soon. Here is my code:



'Save Workbook as "?????"
MyInput = InputBox("What do you want to name the File?")
ActiveWorkbook.SaveAs Filename:= _
"H:\@temp\Daniel B\Current Projects\" & MyInput

'Open Atalanta Codes workbook
Workbooks.Open Filename:="H:\@temp\Daniel B\Reference\Atalanta Codes.xls"

Windows(".XLS").Activate


The last line I would like to change to the Name the user gives the file

Thank you for all your help

[lucas]

Select your code when you post and then hit the vba tags button to enclose in tags for easier reading....

Ok when you say last line are you talking about

Windows(".XLS").Activate

or

Workbooks.Open Filename:="H:\@temp\Daniel B\Reference\Atalanta Codes.xls"

[Djblois]

Windows(".XLS").Activate

[Norie]

Try creating reference(s) to the workbook(s).


Set wb = ActiveWorkbook

MyInput = InputBox("What do you want to name the File?")
wb.SaveAs Filename:="H:\@temp\Daniel B\Current Projects\" & MyInput

'Open Atalanta Codes workbook
Set wbAtl = Workbooks.Open(Filename:="H:\@temp\Daniel B\Reference\Atalanta Codes.xls")

You can now use the references wb and wbAtl whenever you want to refer to either workbook.

[lucas]

I was leaning the same direction Norie:

Sub openit()
Dim MyInput As String
Dim wbA As Workbook
Dim wbB As Workbook
'Save Workbook as "?????"
MyInput = InputBox("What do you want to name the File?")
ActiveWorkbook.SaveAs Filename:= _
"F:\Temp\" & MyInput

Set wbA = Workbooks.Open("F:\Temp\" & MyInput)
Set wbB = Workbooks.Open("F:\Temp\Atalanta Codes.xls")

wbB.Activate
End Sub


Using

wbB.Activate

[Norie]

Steve

There shouldn't actually be any need to use Activate, but then again I've not seen the rest of the code.

[lucas]

So with your code both workbooks would still be open? I think thats what Daniel is trying to do....

[Norie]

Steve

Yes they should both be open.