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