To Keep only 1 header while merging Multiple file

Question

The macro is working OK, but it's copying the header from each file when I intended to copy all the files from a specified folder and paste them into one file. I simply want the header from the first file to be copied.

Sub MergeFiles()

'Declare variables
Dim wb As Workbook
Dim ws As Worksheet
Dim folderPath As String
Dim fileName As String

'Set the folder path where the files are located
folderPath = "C:\ExcelFiles\"

'Create a new workbook to store the combined data
Set wb = Workbooks.Add
Set ws = wb.Sheets(1)

'Loop through each file in the folder
fileName = Dir(folderPath & "*.xlsx")
Do While fileName <> ""
    'Open the file
    Workbooks.Open (folderPath & fileName)
    'Copy the data from the file
    Workbooks(fileName).Sheets(1).Range("A1:Z10000").Copy
    'Paste the data into the master sheet
    ws.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    'Close the file
    Workbooks(fileName).Close
    'Get the next file
    fileName = Dir()
Loop

'Save the master file
wb.SaveAs "C:\ExcelFiles\MasterFile.xlsx"

End Sub

Could you please help to resolve this issue?

Answer 1

There is potential for improvement here, such as determining the table's bottom rather than assuming 10,000 rows are sufficient, but ignoring that, you could:

Include the following in your declarations:

Dim firstfile As Boolean
firstfile = True

and then change your loop like so:

Do While Filename <> ""
    'Open the file
    Workbooks.Open (folderPath & Filename)

    
    If firstfile Then
        'Copy all the data from the file
        Workbooks(Filename).Sheets(1).Range("A1:Z10000").Copy
    Else
        'Copy from 2nd row, the data from the file
        Workbooks(Filename).Sheets(1).Range("A2:Z10000").Copy
    End If
    
    'Paste the data into the master sheet
    ws.Range("A" & ws.Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    'Close the file
    Workbooks(Filename).Close
    'Get the next file
    Filename = Dir()
    firstfile = False
Loop