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.

 

  • 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