Combine workbooks and keep them in there own vba project tree

[noraa]

I would like to combine 2 workbooks and keep their individual vba project seperate in one workbook. I have tried some diffrent vba codes but when they merge all the sheets are mixed on with eachother and the code doesn't work.

 

[Joe4]

I think we need more information in order to help you out:
- How exactly are you combining the workbooks?
- How exactly are you combining the VBA code?

Note in VBA, each sheet has its own VBA module. So you can keep any code associated with specific sheets in the appropriate location.
And you can insert as many General Modules as you like. So if each workbook has their own General Modules, you can insert new ones to keep them separated.

 

[noraa]

Hello

I have tried using VBa codes i found on the internet to combine the 2 workbooks. it puts them in the same VBA project. this is what it looks like with the 2 workbooks open. I would like it to look like this after the merge.

This is what it does look like after merge. Now the Sheets do not function. Is there a way to merge to workbooks and have them work. I would like to do this, so I do not have to open 2 workbooks every time I need to use the impot=rt to put data in the formzilla.

This is the code i used to combine them.

Sub mergeFiles()
'Merges all files in a folder to a main file.

'Define variables:

Dim numberOfFilesChosen, i As Integer Dim tempFileDialog As fileDialog Dim mainWorkbook, sourceWorkbook As Workbook Dim tempWorkSheet As Worksheet

Set mainWorkbook = Application.ActiveWorkbook
Set tempFileDialog = Application.fileDialog(msoFileDialogFilePicker)

'Allow the user to select multiple workbooks

tempFileDialog.AllowMultiSelect = True

numberOfFilesChosen = tempFileDialog.Show

'Loop through all selected workbooks
For i = 1 To tempFileDialog.SelectedItems.Count

'Open each workbook
Workbooks.Open tempFileDialog.SelectedItems(i)

Set sourceWorkbook = ActiveWorkbook

'Copy each worksheet to the end of the main workbook

For Each tempWorkSheet In sourceWorkbook.Worksheets
tempWorkSheet.Copy after:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count)
Next tempWorkSheet

'Close the source workbook
sourceWorkbook.Close

Next i

End Sub

 

[Joe4]

I think you are misunderstanding what you are looking at.
The "VBAProject" correlates directly to the Workbooks (note the name is the name of the Workbook). This tells you what workbook this VBA code is stored in.
There is a 1-1 correspondence between workbooks and VBA projects.
If you combine two workbooks into one, you are left with just one Workbook, and then just one "VBAProject".
Its just the "nature of the beast", by definition.

I don't think you can have two different "VBAProject" records within a single workbook.
What exactly is the "need" for this anyway?

Note that each Workbook has two types of VBA modules:
- Modules attached to the Excel objects (workbook and individual worksheet modules - these are all there by default and cannot be removed).
- "General" modules that you create and add code to. This is where you can customize to add code and organize however you like.

 

[noraa]

The need is I do not want to open the import workbook every time I need to update the purchase order. This will be something multiple people use, and jobs change constantly.

How do I combine them in one workbook and keep the sheets in order in the vba Tree.

The sheets in the Formzilla workbook will move into the Import workbook . When they do merge excel change the sheet number so Sheet 1 Order Formzilla changes to Sheet 7 Order in the merged workbook. Then there is 10 and 11 that are completely out of order. Some of the sheets refer to sheets by number now that no longer works. is there a way to combine them so they continue to work as they did when not combined. eventually there will be many more forms that it will auto populate with their own VBA

 

[Joe4]

How do I combine them in one workbook and keep the sheets in order in the vba Tree.

Why does it matter what order they are in under the VBA Tree?
People who use the VBA code should not really be going into the VB Editor anyway, so they should never see that.
Excel sorts it by the sheet original number, but does it alphabetically, so that "Sheet11" comes before "Sheet2". I agree it looks a little funky, but other than aesthetics, it shouldn't really matter.

I am not sure I understand what the "big picture" is, exactly what you are trying to accomplish by doing this.
Can you explain, in plain English, what exactly you are trying to do?
Why are you merging these workbooks in the first place?
Why do you need to do it more than once?

If we have a clearer understand of what the ultimate goal is here, we may be able to suggest better ways of accomplishing it.

 

[noraa]

I work at a company and we create or forms from a letter template every time we need to do a, price request, a sample request, a submittal form, a purchase order, work orders, billing and on and on,

I created Formzilla workbook to Automatically create these forms. Currently it only does the purchase order. Eventually I want it to do all the forms. For these forms to work I need to import data into the Formzilla workbook that will create the rest of the forms.

I do not want separate excel workbooks for each form. I also do not want a separate workbook for the import. eventually I would like them all in one workbook and import all the information I need for every form so it only needs to be uploaded Once per Job.

I will set it up so that only the form page need will be visible then they can click a button and see the next form they want to create in. There are drop down menus in D4 distributor that will bring up the rest of the info below it to the email. There are also drop down menusfom D10 To D35 that fill in the Description Quantity Containers Price DB row. after the merge the purchasre order no longer works. My thought was that when I merge them the sheet numbers change. In the code some of the modules refer to With Sheet1 or with sheet 4 or with sheet 2.

Here is the first code on sheet1 order Formzilla.


Private Sub Worksheet_Change(ByVal Target As Range)
'On Distributor Name Change
If Not Intersect(Target, Range("E4")) Is Nothing Then
If Target.Value <> Empty And Range("B2").Value <> Empty Then
Dim DistributorRow As Long
DistributorRow = Range("B2").Value 'Distributor Row
Range("E5").Value = Sheet4.Range("E" & DistributorRow).Value 'Quote #
Range("E6").Value = Sheet4.Range("B" & DistributorRow).Value 'Account #
Range("E7").Value = Sheet4.Range("C" & DistributorRow).Value 'Repersenative
Range("E8").Value = Sheet4.Range("D" & DistributorRow).Value 'Email
Range("G2").Value = Sheet4.Range("F" & DistributorRow).Value 'Shipping
End If
End If
'On Order Item Name Change (But not on Order Load)
If Not Intersect(Target, Range("D10:D35")) Is Nothing And Range("B5").Value = False Then
If Range("C" & Target.Row).Value <> Empty Then
Dim ItemRow As Long
ItemRow = Range("C" & Target.Row).Value 'Item Row
Range("E" & Target.Row & ":H" & Target.Row).Value = Sheet5.Range("B" & ItemRow & ":E" & ItemRow).Value 'Copy over Desc, Qty, Unit,Price
End If
End If
End sub
when I merge the 2 workbooks the Sheets from Form Zilla get assigned new page numbers. I assume that is why they are no longer working? it could be something else I do not know.

I need to be able to combine these workbooks so that all of the sheets still work.

 

[Joe4]

Essentially, it looks like what you are doing is trying to create a database in Microsoft Excel. While you can do this, it is very clunky and cumbersome, as that is not really what Excel was designed for.
These kind of projects are usually much easier to do in Microsoft Access (if you know how to use that), where you can have tables, entry forms, queries, and reports.
If you are still early stages of this, it may be worth looking at doing this another way, like using Access.

However, for your immediate problem at hand, you can easily overcome this issue:

when I merge the 2 workbooks the Sheets from Form Zilla get assigned new page numbers. I assume that is why they are no longer working? it could be something else I do not know

Just change how you are referencing the sheets. Instead of using the Sheet index like this:

Sheet4

use the sheet name in your sheet references like this:

Sheets("Import Log")

Because as sheet index numbers may change as you merge workbooks together, sheet names won't!