Strip Macro from copied object ?

I have a macro enabled workbook that creates a new workbook and automatically copies selected sheets and information to the new workbook. (Automated Price Sheet that creates completed order forms)

The created order form contains a couple of drop-downs to select customer type / payment type etc. The drop down and associated list fill range copy fine, allowing the salesperson to change the order form after the fact without creating an entirely new order.

On the Automated sheet there is a small macro triggered by the dropdown change event that automatically selects some options. I would like to strip this macro from the copied dropdown, but still retain the basic functionality.

Currently, when the user attempts to change the dropdown on the newly created order form it gives a warning that the file can not be found (if it has been moved, or it opens the automated price sheet)

Any suggestions?

Thanks

Hello tekman,

For this macro to work, you need to add a reference to the Microsoft Visual Basic for Applications Extensibility object library. The instructions are below if you don't know how to add this reference to your project. The macro will remove the unwanted code and then save a copy of the workbook without the unwanted code. Change the procedure name and worksheet name in the code (they are marked in red) to match what need.

Please Login or Register  to view this content.

Setting a Reference in VBA

1. Open your workbook and then press ALT+F11 to open the VBE.
2. Press ALT+T followed by the Enter key.
3. Scroll down the list until you find Microsoft Visual Basic for Applications Extensibility.
4. Press the Space bar to select it, and press Enter
5. Press CTRL+S to save the reference in the project

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sincerely,
Leith Ross

I appreciate the response.

I will get this implemented this afternoon and let you know how it works