I already posted on stackoverflow

But here's the details again :

I have two spreadsheets that does different things but uses the same input data. Each spreadsheet has it's own form controls and macros and are using a common reference data sets.

Lately, the data being typed in the form controls are increasing and thus, the decision was to combine them into one, so the manual data entry will be done once instead of twice. Also, the reference data used are the same but must be updated for each spreadsheet. By combining the two spreadsheet, only one reference data will be updated and eliminates the discrepancy between the output (i.e. Reports, Graphs).

But two workbooks with macros cannot be easily done. I tried to do the copy and paste approach, but it introduced missing references, also I had to export, then import each VBA module. But seems to introduce a lot of problems.

Is there a way to safely combine multiple workbook into one keeping the formatting, form controls, macros and remove the reference to the "other workbooks"?