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
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