Thread: Writing ActiveWorkbook Name to a variable

Hi

I am struggling with a macro that copies data from one workbook (with a standard name) to another - with a name which can vary.

To explain, I run the macro on a currently open workbook (name could be anything) and open another workbook (name known) and step through the sheets copying various blocks of data across to the first workbook. I need to Activate the 2 workbooks alternatively as I move data back and forth and move from sheet to sheet.

What I can't work out is how to determine in code what the "unknown" workbook name is so that I can make sure I activate the right workbook in the code.

I tried Activating the workbook with the "unknown" name and setting a variable = ActiveWorkbook.Name. However the value of this variable changes during the code run when you activate the other book. Is there any way to collect this value and "freeze" it so it won't change as the code runs?

At the moment I have got round it by ensuring the "unknown" book is the only one open before I start and refer to it as Workbooks(1) in the code.

Any bright ideas would be welcome.

Thanks

Hi,

This is a common requirement. The techique I generally use is to declare a couple of variables:

Dim wbMyBook As Workbook, wbTemp as Workbook

Make the first line of your macro

Set wbMyBook = ActiveWorkbook

then as soon as you've opened the second workbook add the line

Set wbTemp=ActiveWorkbook

Now you can easily refer in code to whichever workbook you want.

HTH