Microsoft excel macros - Workbook names

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

I am having a slight issue with a Macro within excel.

I have a template file, with a working macro which does X Y and Z, (Summary is that it copies lots of cells from a 2nd spreadsheet, pastes it into the template (or current open xls file) then close the 2nd spreadsheet).

The issue I have is that within the macro I use the following line:

Windows("QuoteTemplate.xls").Activate

which means that when I "SaveAs" and change the filename which contains the macro, I get an error because the macro tries to paste the files into the QuoteTemplate.xls, NOT the currently open (and renamed) xls file.

Is there a way to either change the above line to automatically replace it with the current filename [like Windows(this.worksheet).Activate] or when even maybe to change the line when I saveAs (so whatever I saveAs will replace the "QuoteTemplate.xls" with the name I use)

Any help would be much appreciated

Dan


1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
since the macro reside in the template that I guess you resave as save-as. In such case you can try
thisworkbook.activate