Capturing open workbook as variable?

ThisWorkbook should refer to the workbook in which the macro is running.

You can declare a workbook variable:

Please Login or Register  to view this content.

and then set that variable to a workbook using "Workbook.Open":

Please Login or Register  to view this content.

or you can select an already open workbook using its name

Please Login or Register  to view this content.

Thanks a lot gyclone!

Is there the ability to set wb to equal the workbook the operator activates the macro button in?

My concern is that the workbook names will change, and I need it to work autonomously without coding changes if possible.

The biggest problem I see that I'm having is that the macro references the code in the source workbook after it's been imported into the target workbook. If I could get the macro to point to the local code (for want of the proper terminology) instead of pointing back to the source workbook, I think I would avoid having to create a variable.

I'm afraid I'm not sure of the chronology of target vs source vs open. Nor am I sure of whether all the code exists in one workbook or whether you're trying to use one workbook to fire off code in another workbook. "ThisWorkbook", if used in the workbook with the macro button, should capture that workbook. You can use more than one workbook variable and assign each one as each additional workbook opens, as I showed above; just use different names:

Please Login or Register  to view this content.

Once each variable has been assigned, you can use them throughout the procedure.

If you are using a macro in one workbook to launch a macro in another workbook, you can pass the necessary workbook names between the procedures, in the same manners you could if all the code was in the same workbook.

Sorry for not being clearer with the relationships of the files gyclone!

target and open are the same file. This workbook is used by employees to capture data.

Once the worksheet is full, they need to import a new worksheet template from the source workbook that is stored in another location.

Ideally the macro code needs only exist in the target & open workbook - but each of the imported worksheets need to have an active macro button that will allow the employees to once again import another template worksheet from the source workbook.

Everything works fine for the first import, but when I try to import a new worksheet from an imported worksheet it does not work. I believe my issue lies in where I am embedding the code, and how I am referencing it through the macros. I've tried embedding the code in the sheets, and in the workbook & neither worked for me.

What I need is for the macro on an imported sheet to reference the code in the target & open workbook - and not reference back to the source workbook.

I hope this makes it clearer? Thank you!

Sorry, it's been a long day; you should make the following change:

Please Login or Register  to view this content.

should be re-ordered, so we set the wsDestination variable right after we import the template sheet, just to be safe:

Please Login or Register  to view this content.

Please don't apologise mate! You've gone above and beyond to help me out here, thank you so much!

I'm just tweaking the code now, I'll report back a little later to let you know how it goes. You hit the nail right on the head though for how everything is structured. I see my biggest mistake was embedding the code into the sheets and not into the button!

It does not matter. You are welcome.