Merging Workbooks

[naimfarah]

I have 12 workbooks all in the same format and I want to create a one page summary page summarizing my data - is there an easy way to do this? What I need to do is sum up one column and in another column create a weighted average for cost using a volume column (the one in which I am summing) and a price column. I am pulling in volume and creating an average dead net price from the volume and dead net price on each sheet.




Program Volume (Raw Cases) Strike Price Dead Net Price Support per Case Reimbursement ($)
Program 1 $5.00 $5.00 $-
Program 2 $8.00 $8.00 $-

 

[Oaktree]

This is the basic idea I use for these consolidations:

Sub import_data()
Dim i As Long, _
    next_row As Long, _
    master_wkb As Workbook, _
    form_wkb As Workbook, _
    SRfilenames As Variant, _
    fileindex As Integer, _
    filename As String

Set master_wkb = Workbooks(ThisWorkbook.Name)
        
master_wkb.Sheets("Data").Range("2:" & Rows.Count).Delete

'Select files
SRfilenames = Application.GetOpenFilename( _
fileFilter:="Excel,*.xls", _
Title:="Select one or more import files", _
MultiSelect:=True)

Application.ScreenUpdating = False

'Loop through selected files

If Not IsArray(SRfilenames) Then Exit Sub
        
For fileindex = LBound(SRfilenames) To UBound(SRfilenames)
filename = SRfilenames(fileindex)
Workbooks.Open (filename)
Set form_wkb = Workbooks(ActiveWorkbook.Name)
master_wkb.Activate
    
'*******
'*******
'actual import goes here... the code here depends on the arrangement of your input 
'files and desired layout for your master workbook
'*******
'*******

form_wkb.Activate
form_wkb.Close saveChanges:=False
Next fileindex
Application.ScreenUpdating = True
End Sub