VB Excel: How to move Worksheet with Forms/Macros

Question

Hello again. I'm having a problem transferring a worksheet into another workbook. I've written several macros in my first workbook, as well as 1 or 2 forms, and I need to be able to move this worksheet that contains these macros and forms into a new workbook. If I just do a simple worksheet copy, the new version of the worksheet will reference the old worksheet's macros, and the forms I wrote (in the first worksheet) don't launch when I try to load in the second worksheet.

Any suggestions? I've tried googling and there don't seem to be any similar problems... maybe I'm missing something?

Answer 1

To copy the macros and the forms, you need to export them from the original workbook and then import them to the new workbook.

To export, right-click on the relevant form or module in the Project Explorer in the original workbook and choose Export File. This will create a .frm file for a form or a .bas file for a module. You can then go to the new workbook and use Import File in the same way.

If you have used the special modules from the Microsoft Excel Objects section of the explorer (e.g. the module called Sheet1 or the one called ThisWorkbook) you will probably find that these do not import correctly. For those ones, just copy and paste the source code of the macros from one workbook to the other

Answer 2

The only method is as suggested by 'barrowc'. However you may want to judge which one is more easier, moving the macro sheet to the new one or moving sheets from new file to the one with macro :)

Cheers...