Forum Discussion
Joe_official4
May 02, 2023Copper Contributor
COMPILATION OF EXCEL SHEETS FROM A FOLDER
I have a lot of Excel files with the same header in one folder. I want to put all the sheets on one sheet. I want to ignore rows 1 to 5 and use row 6 as the header for all the sheets.
HansVogelaar
May 02, 2023MVP
Here is a macro:
Sub MergeSheets()
' Change the path of the folder; keep the trailing backslash \
Const strFolder = "C:\Excel\Files\"
Dim strFile As String
Dim wbkSource As Workbook
Dim wshSource As Worksheet
Dim lngLastRow As Long
Dim wbkTarget As Workbook
Dim wshTarget As Worksheet
Dim lngTargetRow As Long
Dim blnDone As Boolean
Application.ScreenUpdating = False
Set wbkTarget = Workbooks.Add(xlWBATWorksheet)
Set wshTarget = wbkTarget.Worksheets(1)
lngTargetRow = 1
strFile = Dir(strFolder & "*.xls*")
Do While strFile <> ""
Set wbkSource = Workbooks.Open(strFolder & strFile)
Set wshSource = wbkSource.Worksheets(1)
lngLastRow = wshSource.Cells.Find(What:="*", _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
wshSource.Range("A" & (6 - blnDone) & ":A" & lngLastRow).EntireRow.Copy _
Destination:=wshTarget.Range("A" & lngTargetRow)
lngTargetRow = lngTargetRow + lngLastRow - (6 - blnDone) + 1
blnDone = True
wbkSource.Close SaveChanges:=False
strFile = Dir
Loop
Application.ScreenUpdating = True
End Sub