Exporting/Importing modules between two workbooks

[AnnieLilly]

I have a macro that copies four worksheets from the current workbook, creates a new workbook and then pastes these worksheets into the new workbook.

One of the worksheets refers to a macro that is in the module. How do I export/import the module between the two workbooks. I found some code online that I tried on my macro but when I run it I keep getting a run-time error '9': Subscript out of Range.

Please Login or Register  to view this content.

[Leith Ross]

Hello AnnieLilliy,

You need to set a VBA reference in your workbook with the macro to the Microsoft Visual Basic Extensibility x.x library (x.x is the version on your machine).

Setting a VBA ProjectLibrary Reference
1. Open Excel and press ALT+F11 to open the Visual Basic Editor (VBE).
2. Press ALT+T to activate the Tools Menu.
3. Press r to show the Refernces Dialog.
4. Scroll through the list to find Microsoft Visual Basic Extensibility x.x Object Library
5. Click the Check box to select it. You should see a checkmark.
6. Click OK
7. Press CTRL+S to Save the Reference in your project
8. Press ALT+Q to Close the VBE and return to Excel.

Sincerely,
Leith Ross

[mikerickson]

I don't remember where I got this utilility, but it works well to export/import VBA components, including modules.