Combine multiple worksheets in different files into a single worksheet using macro

[jayexcel1]

Hi,

Data contains multiple workbook files in a folder. The folder will get a workbook file added almost every day.

All the workbook files have Sheet1/Sheet2/Sheet3; but I need the data from only Sheet 1 of every workbook.
Sheet 1 data consists of fixed number of columns but variable number of rows (some might have more than 20,000 rows). Also, column headers for Sheet 1 are fixed for all the workbook files.

I want to combine all the data from Sheet 1 of all the workbooks to a single worksheet so that I can make a summary out of it.

Can someone help me with a simple easy to understand macro.

jay

 

[Smallman]

Hi Jay

This is a really popular question in the Excel VBA space. Here is a method I use.

Option Explicit

Sub OpenImp() 'Excel VBA to open and import data from the Excel files in a directory
    Const sPath = "C:\Test\" 'Change to suit
    Dim sFil As String
    Dim owb As Workbook
    Dim ws As Worksheet
  
    Set ws = Sheet1 'Handy as you don't need to refer to the workbook you start from
    sFil = Dir(sPath & "*.xl*") 'Flexible enough to handle all XL file types

    Do While sFil <> "" 'Only Copies Cols A to F.
        Set owb = Workbooks.Open(sPath & sFil)
        Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("F" & Rows.Count).End(xlUp)).Copy _
        ws.Range("A" & Rows.Count).End(xlUp)(2)
        owb.Close False 'Close no save
        sFil = Dir
    Loop
End Sub

Be sure to change the file path and remember the sheet you are shifting the data to.

Take care

Smallman

 

[jayexcel1]

Thanks a lot for the quick reply.
I tried using the above code (changed the file path); opened a new workbook and inserted a module in which I copied the code.

The output I received was not that I expected. It just returned back only the first record from all the workbooks' Sheet 1. Also, the column headers gets repeatedly copied to the output.

I have attached a sample data and the resultant output file.

Just to reiterate: My actual data too would have number of columns fixed at 4 (A1:D1), but number of rows could vary.

jay

 

[Smallman]

Hi Jay

That is OK. I just provided a sample range for you to upload. Where it says only copies columns A-F.

In the code where it says

.Range("F"

change this to

.Range("D"

This will solve your problem and all rows will be returned no matter how large or small your dataset is. Changing this yourself will help with the learning process.

Take care

Smallman

 

[gaurav99]

hey.. thanks for posting the code. But I am also facing the same issue faced by Jay. Even after editing the range, Code still returns some rows from file 1 and just headers from remaining 4 files.

I would have eventually around 20 files with multiple worksheets. I am trying to compile data from specific worksheet with around 40 columns.

Many thanks in advance !