Solved: Combine multiple workbooks from various locations into one master workbook

[XLGibbs]

It may be easier just to cycle through the OPEN workbooks, as you can have all of them open at once to do this.......would something like that work...? The below will copy sheet in 1st position from all open workbooks into the destination workbook.

[VBA]
Dim Wkb1 As Workbook, Wkb2 As Workbook, wb As Workbook
Set Wkb1 = Workbooks("Destination.xls")

'loop through the open files
'
counter = 1 'set counter at 1
For Each wb In Workbooks
If wb.Name <> "PERSONAL.XLS" And wb.Name <> Wkb1.Name Then
wb.Activate

wb.Sheets(1).Copy Wkb1.Sheets.Add

counter = counter + 1
Application.DisplayAlerts = False
wb.Close
End If
Next wb

Wkb1.Activate
MsgBox counter & " Files were copied into Destination"

End Sub
[/VBA]

Just change the name of your destination file, activate the destination file and sheet 1 will copy into that workbook...

for multiple sheets
[VBA]
For each w in wb.Worksheets
w.Copy Wkb1.Sheets.Add
Next w
[/VBA]

[Shazam]

Hey Gibbs thanks for replying. Well The code you see below is exactly what I'm looking for its by DJR. But I cant figured out how to adjust it to open the last modified file in thoses folders.

I tried to stick this line code in DJR code but it does not work. Am I missing something?

[VBA].LastModified = msoLastModifiedAnyTime[/VBA]




[VBA]
Option Explicit

Sub CombineFiles()

Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim WS As Worksheet

Application.EnableEvents = False
Application.ScreenUpdating = False
Path = "Z:\Performance\Daily Data\Sample\" 'Change as needed
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)
Next WS
Wkb.Close False
FileName = Dir()
Loop

Path = "Z:\Performance\Daily Charts\Test\" 'Change as needed
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)
Next WS
Wkb.Close False
FileName = Dir()
Loop

Path = "Z:\Maker\" 'Change as needed
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)
Next WS
Wkb.Close False
FileName = Dir()
Loop

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub
[/VBA]