Windows("file name always different").Activate cant reference the workbook in my MACRO !!

[michaelproctor001]

All
My macro is not working and im at a loss

I have 2 workbooks “FORM” and “EXPORT MASTER” and essentially FORM is for entering data and EXPORT MASTER is for collecting it
Unfortunately because all users need to print FORM and get a signature on it before sending it on to a different department to then bring the data back up using the unique reference number that’s created this is the only way it can be done and explaining the ins and outs of the 20 departments it affects would take me a long time so your gonna have to trust me theres no way around it.


When users have completed all information in FORM and start the macro then FORM is saved as a new filename which changes every time (and is taken from Cell AV36) using the below code……

Please Login or Register  to view this content.

Then EXPORT MASTER workbook is activated and a unique reference number created which is then copied…….

Please Login or Register  to view this content.

This is where my problem occurs…..

Please Login or Register  to view this content.

As I try to refer back to FORM under its new filename I do not know how to refer to it correctly so I can paste the unique number back
I have tried many different versions of
Windows("SAVENAME2").Activate
But none seem to work

After I have got back to it then the rest of the macro worsk fine…

Please Login or Register  to view this content.

I wish to add more but will not complicate the macro any further until ive fixed this part

Many thanks in advance for your help 

Michael

[Norie]

Michael

Create a reference to the workbook before you change it's name.

Then you can use that reference throughout the code to refer to it.

Oh, and could you edit your post to add code tags, that will make easier to tell the code and message text apart.

[michaelproctor001]

Thanks so much I'll Google how to do that
as you can tell I'm a little new.
if you want to enlighten me that would also be fine

[VBA FTW]

Is the file you're trying to activate open on your desktop? If so, you need to reference it without the file path. Looks like you need a cell in your workbook that just has the workbookname with its extension. Then create a variable and set it equal to that cell, and activate that variable rather than SAVENAME2. Sorry if I'm wrong, but that's what it looks like to me


EDIT: looking further, you may be able to just swap out SAVENAME2.Activate with SAVENAME1.Activate ....I would try that before anything

[Norie]

Michael

It's kind of hard to discern what's code and what's not, could you please add code tags?

Anyway to create a reference to the active workbook you would use this code.

Please Login or Register  to view this content.

You can then use the reference wbAct in the rest of the code, regardless of the name of the workbook.

[michaelproctor001]

All good stuff thanks

the workbook will indeed always be open

I am as mentioned very new to this and looking to learn rather than someone to do it all for me

How do I say this workbook = "this" regardless of the random filename I've created then I could refer back to it.

problem is some users will have on there desktop but others will always use the master template.

mike

[michaelproctor001]

Like maybe some kind of "thisworkbook" function

mike

[Norie]

To refer to the workbook the code is in you can use ThisWorkbook.

[michaelproctor001]

many thanks for all your help

in the end i simply used

ThisWorkbook.Activate

as i have learnt if you are refering to the workbook the code / macro is in then this will refer to it

the simplest solutions are always the best

mike

[Norie]

Mike

There should be no need to use Activate.