To Keep only 1 header while merging Multiple file-CodePudding

I wanted to copy all the files from the specific folder and past it into 1 file, macro is running fine but it’s coping header from each file. I want header to be copied from 1st file only.

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

CodePudding user response：

There's some room for improvement here, such as finding the bottom of the table instead of assuming 10,000 rows is enough but - ignoring that - you could:

Add this with 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