Forum Discussion

Joe_official4's avatar
Joe_official4
Copper Contributor
May 02, 2023

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.  
excel
HansVogelaar's avatar
HansVogelaar
MVP
May 02, 2023

Joe_official4 

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

Resources