Option Explicit
Sub CombineWorkbooks()
Dim oFile As Object
Dim oFiles As Object
Dim oFolder As Object
Dim NewFile As String
Dim Path As Variant
Dim RngDst As Range
Dim RngSrc As Range
Dim WkNum As Integer
Dim WkbDst As Workbook
Dim WkbSrc As Workbook
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select Weekly Folder"
If .Show Then Path = .SelectedItems(1) Else Exit Sub
End With
' // Ask the user for the week number.
WkNum = InputBox("Enter the Week number of the folder.")
If WkNum = 0 Then Exit Sub
' // Name of New Workbook.
NewFile = Path & "\Week " & WkNum & ".xlsx"
' // Create the Destination workbook and assign it's object variables.
Set WkbDst = Workbooks.Add(xlWBATWorksheet)
Set RngDst = WkbDst.Worksheets("Sheet1").Range("A1")
With CreateObject("Shell.Aplication")
Set oFolder = .Namespace(Path)
Set oFiles = oFolder.Items
End With
' // Open each workbook in the folder and copy it to the new workbook.
For Each oFile In oFiles
'WkDay = Format(WkBeg + n, "ddd")
Set WkbSrc = Workbooks.Open(oFile)
Set RngSrc = WkbSrc.Worksheets(1).UsedRange
RngSrc.Copy Destination:=RngDst
Set RngDst = RngDst.Offset(RngSrc.Rows)
WkbSrc.Close SaveChanges:=False
Next oFile
' // Save the New Workbook and close it.
WkbDst.SaveAs NewFile
WkbDst.Close
End SubOption Explicit
Sub CombineWorkbooks()
Dim DstFile As String
Dim LastRow As Long
Dim oFile As Object
Dim oFiles As Object
Dim oFolder As Object
Dim NewFile As String
Dim Path As Variant
Dim RngDst As Range
Dim RngSrc As Range
Dim WkNum As Variant
Dim WkbDst As Workbook
Dim WkbSrc As Workbook
' // Source path for the workbooks.
Path = "S:\freddy\Harlow\Daily Debrief Night Folder\Debriefs\" _
& Year(Now) & "\" & Month(Now) & " - " & Format(Now, "mmmm") & "\"
InputWeekNumber:
' // Ask the user for the week number.
WkNum = InputBox("Enter the Week number of the folder.")
If WkNum = "" Then Exit Sub
WkNum = Int(Val(WkNum))
If WkNum < 1 Or WkNum > 52 Then
MsgBox "You have entered an invalid week number."
GoTo InputWeekNumber
End If
' // Finish the source folder path using the week number.
Path = Path & "Week " & WkNum
' // Full path of destination Workbook.
DstFile = "S:\freddy\Harlow\Daily Debrief Night Folder\hours tracker\harlow hours tracker master" & ".xlsx"
' // Create the Destination workbook and assign it's object variables.
Set WkbDst = Workbooks.Open(DstFile)
LastRow = WkbDst.Worksheets("Sheet1").Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False, False, False).Row
' // Enter new data 1 row below the last row with data.
Set WksRng = WkbDst.Cells(LastRow, "A").Offset(1, 0)
' // Get all the files in the source folder.
With CreateObject("Shell.Aplication")
Set oFolder = .Namespace(Path)
Set oFiles = oFolder.Items
oFiles.Filter 64, "Harlow debrief *.*"
End With
' // Open each workbook in the source folder and copy it to the new workbook.
For Each oFile In oFiles
Set WkbSrc = Workbooks.Open(oFile)
Set RngSrc = WkbSrc.Worksheets("Plan").UsedRange
RngSrc.Copy Destination:=RngDst
Set RngDst = RngDst.Offset(RngSrc.Rows)
WkbSrc.Close SaveChanges:=False
Next oFile
WkbDst.Close SaveChanges:=True
End Sub
