MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Copy Workbooks & Macros


Posted by Suz on August 02, 2001 9:25 AM

I wrote a macro in a workbook that opens a second workbook, and then repeatedly switches back and forth between the two. My problem - the first file is only a template which I rename for each job. Is there a way to make the macro in the template recognize a new file name without editing each reference?


Posted by Ivan F Moala on August 03, 2001 3:31 AM

This is when you use the workbooks collection
code names.

ie In your template you should be using code
such as Thisworkbook. & any other workbook
you should be referencing via Activworkbook
or the workbook index and then assigning to variables.......
you should use the ThisWorkbook property to qualify any statement that must be run on the workbook you compile into the add-in. The ThisWorkbook property returns the workbook where the Visual Basic code is running. In most cases, this is the same as the active workbook. However, if the Visual Basic code is part of an add-in, the ThisWorkbook property wonÂ’t return the active workbook. In this case, the active workbook is the workbook calling the add-in, whereas the ThisWorkbook property returns the add-in workbook.

Not sure if this is clear ???


Ivan