Macro Add-in that inserts, runs and removes a module in the active workbook

[patsdavixen]

Hi,

I have a macro that copies data from several workbooks saved in a particular folder to a master workbook.
The problem is that this macro only works when it is included as a module in the master workbook. Unfortunately, I cannot ask the users of this macro to insert a module, copy the code and run it each time they want to consolidate the master workbook. Therefore, I wanted to use this code by including it in a Add-In that I already prepared. The problem is that this consolidation macro does not run when it is in an add-in. Could anyone please help me create a code that I can include in a macro add in that would insert a module in the master file, include the code that I already have, run it and then delete the module? The add-in would be password protected. Any help would be appreciated.

Thanks,
Pat

 

[Andrew Poulsom]

In what way doesn't your macro work when it's in an Add-In?

 

[patsdavixen]

Hi Andrew,

I had a different question that I posted on this forum and received a perfect code. Here is the link:http://www.mrexcel.com/forum/excel-...kbooks-into-master-file-loop.html#post3427313

The problem is that the final code that I included in my earlier post (at the above mentioned link) only works when it is inserted as a module in the active workbook.
I've mentioned details of the error on the aforementioned thread as well.
I've started a new thread as this really becomes a new questions that I thought would benefit many.
I hope that's alright.

Thanks,
Pat

 

[Andrew Poulsom]

Try changing this line:

Set wbM = ThisWorkbook

to:

Set wbM = ActiveWorkbook

 

[patsdavixen]

Hi Andrew,

Thank you so much for your help You are a life saver.
A point to note for anyone who wants to use this code is that you need to keep the Add-In and the master file in the same folder since the consolidated master file is saved where ever the Add-In is saved. The Add-In is now the Active workbook.

Thank you,
Pat

 

[Andrew Poulsom]

The Add-In is now the Active workbook.

An Add-In can never be the active workbook.

 

[patsdavixen]

I apologize for the "active workbook" term. I meant that the location (path) of the Add-in needs to be the same path and folder folder that you want the master file to be saved at. The master file is saved where ever the Add-In is located.

 

[Andrew Poulsom]

I don't see why that would be the case, unless you are using the Add-In's path in your code.