Switching between workbooks in vba

[Ceri_m]

Hi there, I have a problem I've been trying to work out for a while, I'm trying to switch between workbooks, Workbook1 with the code in will have a constant name, the name of workbook2 will change every time the report is run (on a weekly basis).
I've got workbook1 to open workbook2, and can do stuff in there then return to workbook1 to paste values in etc, but can't get it to return to workbook2 later on without having to close the file and re-open it.
I'm basing the name of Workbook2 on the value of a cell in workbook one, so just need to be able to activate that workbook from a command in workbook1 linking to the cell.
Is this possible? I'm not very good at the vba stuff, and make most of my macros by recording them, but then get stuck when I come to something like this!!
Any help would be appreaciated! Thanks
Ceri

 

[Soggy]

When you first open workbook2 you need to assign a variable to pick up the name of the book.

Not sure how much you know if you are recording so I will try to explain as I go along:

To create a variable just under you macro name (it doesn't need to be there but i find keeping variable in the same place helpfull) state:

Dim OthWB as string

Then after opening the workbook in your code input:

OthWB = ActiveWorkbook.Name

this will assign the name of the workbook open at the time (Workbook2).

Every time you wish to activate this workbook use:

Workbooks(OthWB).Activate

to go back to the original replace the OthWB with the name of the work book in "" (The " is not needed with variables).

Hope this makes sense.

 

[Ceri_m]

Thank you, that worked great!