Consolidating data from multiple workbooks into a master

[piernt]

Hello. I have only been working with macros/VBA for a week, so I know there are simple issues with my code, but I've turned it into spaghetti code trying to debug.
I am trying to load data from multiple workbooks into a master and really need help please!!

Dir for source files: C:\Test Dir\
Dir for Master: C:\Test Dir\Master\

Source filenames differ, but all end in "*FORMATTED.xlsx."
Master filename: "Payroll Master.xlsx"
Source worksheet name = "Loaded Data"
All SOURCE data is in rows 2-106 and columns A through J.

The Master file has the same column headers as the source files and I am loading all data into the "Summary" worksheet.

My latest error is a runtime 91, where MyTemplate is defined. Please help!

This is my current code:

Sub combine_data()
'
Dim MyPath As String
Dim SumPath As String
Dim MyName As String
Dim SumName As String
Dim MyTemplate As Workbook
Dim SumTemplate As Workbook
MyPath = "C:\Test Dir\"
SumPath = "C:\Test Dir\Master\"
MyTemplate = "*.xlsx" 'Set the template.
SumTemplate = "Payroll MASTER.xlsx"
MyName = Dir(MyPath & MyTemplate) 'Retrieve the first file
SumName = Dir(SumPath & SumTemplate)

Do While MyName <> ""
Workbooks.Open (MyName)
Sheets("Loaded Data").Range("A2:J106").Copy
'Selection.Copy
Workbooks.Open (SumName)
Range("A65536").End(xlUp).Offset(1, 0).Activate
Selection.PasteSpecial Paste:=xlPasteValues
Workbooks(MyName).Close SaveChanges:=False 'close
Workbooks(SumName).Close SaveChanges:=True
MyName = Dir 'Get next file
Loop
End Sub

 

[Rijnsent]

Hi Piernt,

it does look like you're on the right track. I would open the summary file only once, then open the source files one by one. I made some small modifications to your code, wasn't able to test it, but hope that this gets you moving again:

Cheers,

Koen

Sub combine_data()
'
Dim MyPath As String
Dim SumPath As String
Dim MyName As String
Dim SumName As String
Dim MyTemplate As Workbook
Dim SumTemplate As Workbook

MyPath = "C:\Test Dir\"
SumPath = "C:\Test Dir\Master\"

MyTemplate = "*.xlsx" 'Set the template.
SumTemplate = "Payroll MASTER.xlsx"

MyName = Dir(MyPath & MyTemplate) 'Retrieve the first file
Set SumWb = Workbooks.Open(SumPath & SumTemplate)

Do While MyName <> ""
    Set ImpWb = Workbooks.Open(MyName, False, True)
    
    ImpWb.Worksheets("Loaded Data").Range("A2:J106").Copy
    SumWb.Worksheets("ResultSheet").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    
    Workbooks(MyName).Close SaveChanges:=False 'close
    
    MyName = Dir 'Get next file
    Set ImpWb = Nothing
Loop

SumWb.Close SaveChanges:=True
    
Set SumWb = Nothing
Set ImpWb = Nothing

End Sub

 

[piernt]

All set!! Thank you!