Manipulating ActiveWorkbook when calling macro from Personal Macro Workbook

hello! I'm attempting to design a macro that opens an existing workbook, copies a formatted worksheet ("revisions log") into the active workbook as the last sheet, and closes the original source workbook. because I will be calling this macro in multiple workbooks, I need to set the target workbook (variable name TargetFile) as the ActiveWorkbook and will need to store the macro in the Personal Macro Workbook. problem is, when I'm calling a macro from my Personal Macro Workbook, that then becomes the ActiveWorkbook. the attempt to copy the worksheet stops in its tracks.

I've pasted the code below that does exactly what I want when put in a project module that isn't the Personal Macro Workbook (e.g., "Book1.xlsm"). any suggestions on how to get Excel to recognize that the workbook that was active prior to calling the sub-procedure is what I intend to assign to variable TargetFile?

Please Login or Register  to view this content.

hi sintek, thanks for your suggestion. let me quickly explain with a bit more detail. I work as a data analyst and build tools for other data analysts in my organization. as I'm building these tools, I want to include a revisions log hidden in the background that I can edit as I make changes, to keep track of when and what I've adjusted over time. so as I'm opening up a new workbook to start building a tool, I want to be able to run a macro straight away that will open up the source file, copy the revisions log worksheet that's formatted how I like it, copy it into the new workbook (TargetFile) then close the source file. I don't want the code to activate the Personal Macro Workbook upon completion; I want it to reference back the new workbook I have open that may or may not have been saved with a name by this stage. there will not be multiple sheets named Revisions, so I don't see a need for loops as it should be a one-iteration action for each workbook.

that said, it appears that calling a macro saved in the Personal Macro Workbook marks that workbook the active workbook, which then means in my code, when I set the following ...

Please Login or Register  to view this content.

... I'm accidentally setting the Personal Macro Workbook as my ActiveWorkbook instead of the intended workbook. not sure how to rectify this, unless there's a VBA code to activate the most recently activated workbook.. I could sneak that line just before the code embedded in the previous blockquote. but I have no idea if such a command exists or is possible.

of course there may be a better solution.. just not sure what it would be?

this macro does declare the active workbook before opening a second workbook. the problem was calling a macro stored in the Personal Macro Workbook. the second the macro began to execute, the active window shifted from Book1.xlsx (or whatever file I intended to work in) to the Personal Macro Workbook, meaning the macro was declaring the PMW to TargetFile instead of the intended "Book1.xlsx". the fix was to tell the macro to first switch to the previous active window prior to declaring, as that re-focused the attention on "Book1.xlsx" -- that was the bit of code I was missing.