How do I change a Macro to use ThisWorkbook properly?

Hi,

I created the following macro by recording the macro and going through the steps manually, however I need to make some changes and can't seem to accomplish what I'm trying to do.

The Macro opens a master inventory file, creates a new line, and then links certain column cells in the inventory to corresponding places within the original form (the macro is executed from the original form once it's completed).

Problem is, the macro is written using the form "template" so whenever I save the template as the name of the unique item, it won't update the macro language as well.

What I'm trying to accomplish is when someone opens the template, the save immediately with a different file name, and once the form is completed and the macro is run, it's creating the new line in the inventory pointing to that specific file.

I thought somehow utilizing ThisWorkbook within the macro instead of explicitly using something like "='[Control Sheets (JOHN TEST).xls]FUND SET_UP PG_1'!R2C3" would accomplish what I'm trying to do.

Any help would be greatly appreciated. Here's the code...

Please Login or Register  to view this content.

Thanks

Please Login or Register  to view this content.

Now wb will always refer to that specific workbook within your code. you can us it in many ways e.g. wb.close,

You can do the same with a specific sheet...

Please Login or Register  to view this content.

This gives you the ability to refer to exactly that sheet. Examples of use include:

Please Login or Register  to view this content.

Hopefully this helps you get started.

Change this...
"='[Control Sheets (JOHN TEST).xls]FUND SET_UP PG_1'!R2C3"

To this...
"='[" & ThisWorkbook.Name & "]FUND SET_UP PG_1'!R2C3"

Please Login or Register  to view this content.

TIP: in the VBA editor, highlight the Open keyword in the code and press F1 for help\syntax\example code.

The second part is too vague; what exists where? How is it tested for?