Forum Discussion
I need advice on merging two workbooks, both containing VBA code
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.
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.
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.
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.
- 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.
