Writing ActiveWorkbook Name To A Variable

[kwesmc1]

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? I get a Run-Time error '9' Subscript out of Range on the line: ActiveWorkbook.Sheets("Weekly Food Cost").Activate
Any help would be greatly appreciated
Here is the code:

Please Login or Register  to view this content.

[AlphaFrog]

I cannot devise what you want done from your code.

Below is example code of how to assign a workbook\worksheet to a variable, copy values between workbooks without activating or selecting, and close the original workbook. Perhaps you can incorporate the example to your situation.

Please Login or Register  to view this content.

[kwesmc1]

Thank you very much. I will give that a try.