CODEWITHSUNDEEP
excelvba
khyati dedhia
khyati dedhia

Reputation: 81

Keep only one header while merging multiple files

I want to copy all the files from the specific folder and paste into one file. Macro is copying header from each file. I want header to be copied from first 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

Upvotes: 0

Views: 54

Answers (1)

CLR
CLR

Reputation: 12279

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

Upvotes: 1

Related Questions