VBA: Ability to store a value for reference further down the code/module?

[Alex-Weazel]

Hello All, first post, be gentle
____

Scenario: Book1 is a form. Book 2 is a data storage file.

I have a macro that copies the values input into Book1 into a single line within Book2. The macro then also needs to Save As Book1 but with the value of a specific cell (Say, ABC.xls).

(Not a problem so far...)

My problem arises where my users want to occasionally go back to a saved file (ABC.xls), update some values and resend the data to Book2. Of course my storage macro references "Book1.xls" whenever it needs to switch back to copy the cells so the procedure stops.

Is there a function or procedure that stores the Active Workbook's filename so that the code can reference that value and not use Windows("Book1.xls").Activate as it does at the moment?

Thanks for any insights, Alex.

 

[Norie]

Alex

Well the first thing to do is to try and not use Activate and not 'switch' between workbooks/worksheets.

You can easily create workbook references.

Set wbThis = ThisWorkbook ' create reference to workbook code is in
 
Set wbActive = ActiveWorkbook ' create reference to the active workbook
 
Set wbOpen = Workbooks.Open("C:\AWorkbook.xls") ' open a workbook and create a reference to it

All of the above can be used in subsequent code, how exactly depends on what you are actually trying to do.

Can you post your current code?

 

[Alex-Weazel]

Thanks Norie, that ThisWorkbook function was just what I was looking for!

Apologies for the sloppy description, unfortunately a lot of my original code was Recorded