An the accounting department of an insurance company had daily financial reporting that was automated by their IT department providing daily reports to a network drive for seven software systems. 7 software systems x 30 working days per month = 210 individual files. Each of these files, per software system, needed to be opened as a worksheet in a single workbook in order to run plug in the formulas they already created.
They were manually opening each file, copying a few hundred lines, creating a new tab in their workbook, naming the tab (complete with typos) and repeating this process 200+ times to create consolidated reporting. This script does all the merging and renaming for them. It saves a few hours from a senior accountant once a month.
Try downloading the file and using it first since there are some cells that are referenced in the VBA.
Sub CombineFiles()
Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim WS As Worksheet
Dim prependwbname As String
Dim cleanname As String
Dim splitarr As Variant
prependwbname = ThisWorkbook.Sheets(1).Range("F9").Value
Application.EnableEvents = False
Application.ScreenUpdating = False
Path = ThisWorkbook.Sheets(1).Range("C4").Value
FileName = Dir(Path & "\*.xls", vbNormal)
Do Until FileName = ""
Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)
For Each WS In Wkb.Worksheets
WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.count)
If prependwbname = "Yes" Then splitarr = Split(FileName, ".")
If prependwbname = "Yes" Then cleanname = splitarr(1)
If prependwbname = "Yes" Then ActiveSheet.Name = cleanname & "-" & ActiveSheet.Name
Next WS
Wkb.Close False
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Feel free to modify the above script to fit your needs.