I need advice on merging two workbooks, both containing VBA code

[perkin_warbeck]

There are dozens of web sites that tell you how to merge multiple workbooks into a single workbook, but none of them considers that the workbooks might contain VBA code. I want to merge two workbooks, and both contain code. 

Project1 has a single sheet but multiple code modules: 

• Module1 (code)
• Sheet1 (code)
• ThisWorkbook (code)
• CChartEvent (code)

Project2 has several sheets (all with default names, Sheet1, Sheet2, etc.) but only one code module

• Module1 (code)

I can rename sheets so they are unique across the two projects, and I can use the VBA Properties to rename modules (Module1) so their names don't conflict.  If I open both projects in the VBA editor, can I simply create a new project (Project3), and copy/paste components from Project1 and Project2 into Project 3? Or is there a better/safer way? 

I'm worried about things like the possibilty that named range, subroutine names, etc. may conflict. 

[#2 · Accepted solution]

@perkin_warbeck 

You will have to check defined names before merging.

Based on your description, I'd create a copy of the first workbook, either in File Explorer from outside Excel, or by using File > Save As from within Excel.

Open the copy and the second workbook.

You can then copy or move the sheets from the second workbook into the copy of the first one.

In the Visual Basic Editor, drag Module1 from the VBA project of the second workbook and drop it onto the VBA project of the other one. It will be renamed automatically.

[#3]

@Hans Vogelaar 

I tried your suggestion on a small prototype and found a potential problem. I know it's a bad form, but I don't name all my ranges. My code uses many explicit ranges like Range("Sheet1!A1:A10"). Here is what happens as a result:

After Sheet1 is copied from Project2 into the Project1-copy, it becomes a unique sheet named Sheet1(2). When module1 from Project 2 is copied into Project1-copy, it becomes a unique module named module11. So far, so it's working very well. However explicit range references to Sheet1 in module11 now refer to Sheet1, not Sheet1(2).

Is the only way to avoid this problem to use defined names for all ranges? I would do that, but Name Manager makes it difficult to manage more than just a few names because it insists on alphabetizing them.

[#4]

@perkin_warbeck 

You could give the sheet in workbook #2 a unique name before moving it.

Then use the Replace dialog in the Visual Basic Editor to replace all instances of the old sheet name with the new sheet name.

[#5]

Of course, why didn't I think of that? I have learned more from you in this forum than from all the Excel books on my shelf and videos on the Web. Thank you.