To Keep only 1 header while merging Multiple file

0 votes

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?

Jan 31, 2023 in Others by Kithuzzz
• 38,010 points
689 views

1 answer to this question.

0 votes

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
answered Jan 31, 2023 by narikkadan
• 63,420 points

Related Questions In Others

+1 vote
0 answers

where to put tf files while building servers from terraform.. its creating only workspace.

# kitchen create -----> Starting Kitchen (v2.2.5) -----> Creating ...READ MORE

Aug 4, 2019 in Others by Priyanka
449 views
0 votes
0 answers

I Want To Print 1 to 100 Numbers Using Arrays In Javascript Only

Please bear with me as I am ...READ MORE

Aug 18, 2022 in Others by krishna
• 2,820 points
884 views
0 votes
1 answer

Rails - Export records to downloadable excel file using axlsx gem (Keep MVC)

Use  axlsx_rails Gem with the template. In my case, ...READ MORE

answered Nov 19, 2022 in Others by narikkadan
• 63,420 points
1,650 views
0 votes
1 answer

How do I merge multiple excel files to a single excel file

You copy a worksheet from before each ...READ MORE

answered Dec 24, 2022 in Others by narikkadan
• 63,420 points
595 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
896 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,212 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
504 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
750 views
0 votes
1 answer

How to split only one column to multiple rows while copying rest of the columns along the way?

Try this: =DROP(REDUCE(0,REDUCE(0,C2:C3,LAMBDA(a,x,VSTACK(a,CONCAT ...READ MORE

answered Feb 6, 2023 in Others by narikkadan
• 63,420 points
256 views
0 votes
1 answer

How can I convert excel to PDF by Libreoffice and keep all format from excel file?

"Times New Roman" typeface does not have ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,420 points
1,292 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP