consolidate data

I have 1500 xlsx files
i have to consolidate the data by vba
i want all 1500 files data into one sheet
my all files have only sheet1
what i want is copy all 1500 files sheet1 data and consolidate that data into one sheet
my all files are located in(path ) -   C:\Users\user\Desktop\New folder
Avinash SinghAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
How many rows are in your worksheets?
0
Bill PrewCommented:
Expand on what you mean by "consolidate" the date?  Is that just adding each additional exiting files rows to the bottom of the new merged sheet?  Do all 1500 that exist share the exact same column layout?  Is any selection or processing needed?  Are there header rows?

Uploading exaples would help you get better results.


»bp
0
aikimarkCommented:
You also have a question about 1500 CSV files.  Are these duplicate questions?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Roy CoxGroup Finance ManagerCommented:
Try this. It assumes that the data starts in A1 and has header rows. Let me know if it needs amending


Option Explicit


'---------------------------------------------------------------------------------------
' Module    : Data
' Author    : Roy Cox (royUK)
' Website   : for more examples and Excel Consulting
' Date      : 19/11/2011
' Purpose   : Combine data from several workbooks
' Disclaimer: Disclaimer; This code is offered as is with no guarantees. You may use it in your
'             projects but please leave this header intact.

'---------------------------------------------------------------------------------------



Sub CombineData()
    Dim oWbk As Workbook, rRng As Range, rToCopy As Range, rNextCl As Range
    Dim bHeaders As Boolean
    Dim sFil As String, sPath As String

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
        ''///  On Error GoTo exithandler

        sPath = "C:\Users\user\Desktop\New folder"
        ChDir sPath
        sFil = Dir("*.xl**")    ''///file type
        Do While sFil <> ""    ''///will start LOOP until all files in folder sPath have been looped through

            With ThisWorkbook.Worksheets(1)
                Set rRng = .Range("A1").CurrentRegion    ''///change range here
                If rRng.Cells.Count = 0 Then
                    ''///no data in master sheet
                    bHeaders = False
                Else: bHeaders = True
                End If

                Set oWbk = Workbooks.Open(sPath & Application.PathSeparator & sFil)    ''///opens the file
                ''///A1 must be within the data, if not amend the Range below
                Set rToCopy = oWbk.ActiveSheet.Range("A1").CurrentRegion
                If Not bHeaders Then
                    Set rNextCl = .Cells(1, 1)
                    bHeaders = True
                Else: Set rNextCl = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
                    ''///headers exist so don't copy
                    Set rToCopy = rToCopy.Offset(1, 0).Resize(rToCopy.Rows.Count - 1, _
                                                              rToCopy.Columns.Count)
                End If
                rToCopy.Copy rNextCl
            End With
            oWbk.Close False     ''///close source workbook
            sFil = Dir
        Loop    ''///End of LOOP

exithandler:
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
    End With
End Sub

Open in new window

0
Avinash SinghAuthor Commented:
this code is perfect Sir, but it leaves the first row of sheet1 of all files and i don't want to miss that also so look into it copy complete sheet1 of all files and consolidate the data into 1 file  in sheet1 , i don't  want to miss anything
0
Roy CoxGroup Finance ManagerCommented:
So there is no header row?
0
Roy CoxGroup Finance ManagerCommented:
Try this

Option Explicit


'---------------------------------------------------------------------------------------
' Module    : Data
' Author    : Roy Cox (royUK)
' Website   : for more examples and Excel Consulting
' Date      : 19/11/2011
' Purpose   : Combine data from several workbooks
' Disclaimer: Disclaimer; This code is offered as is with no guarantees. You may use it in your
'             projects but please leave this header intact.

'---------------------------------------------------------------------------------------



Sub CombineData()
    Dim oWbk As Workbook, rRng As Range, rToCopy As Range, rNextCl As Range
    Dim sFil As String, sPath As String

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
        ''///  On Error GoTo exithandler

        sPath = "C:\Users\user\Desktop\New folder"
        ChDir sPath
        sFil = Dir("*.xl**")    ''///file type
        Do While sFil <> ""    ''///will start LOOP until all files in folder sPath have been looped through

            With ThisWorkbook.Worksheets(1)
                Set oWbk = Workbooks.Open(sPath & Application.PathSeparator & sFil)    ''///opens the file
                ''///A1 must be within the data, if not amend the Range below
                Set rToCopy = oWbk.Sheet1.Range("A1").CurrentRegion
                Set rNextCl = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
                rToCopy.Copy rNextCl
            End With
            oWbk.Close False     ''///close source workbook
            sFil = Dir
        Loop    ''///End of LOOP

exithandler:
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
    End With
End Sub

Open in new window

0
Avinash SinghAuthor Commented:
This code is not perfect
0
aikimarkCommented:
This code is not perfect
...but it is free
1
Roy CoxGroup Finance ManagerCommented:
What do you mean it is not perfect,? It has helped many people on many Forums.

This code is not perfect
Report Comment

If you need the code adjusting you might consider thanking the provider and stating what you want changing.
0
Avinash SinghAuthor Commented:
Sorry Roy sir i mean to say that this code is not working
Thnx all of u for giving ur precious time and great support  to this post
here is the code

Sub STEP2INSHEET1()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")
 
'change folder path of excel files here
Set dirObj = mergeObj.Getfolder("D:\Data\XLS")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
 
'change "A2" with cell reference of start point for every files here
'for example "B3:IV" to merge all files start from columns B and rows 3
'If you're files using more than IV column, change it to the latest column
'Also change "A" column on "A65536" to the same column as start point
Range("A1:IV" & Range("A1048576").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate
 
'Do not change the following column. It's not the same column as above
Range("A1048576").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End Sub
0
Roy CoxGroup Finance ManagerCommented:
That code bears no resemblance to mine, which you said was working apart from missing a line because I assumed that the data had header rows. I amended the code so that it should work
0
Roy CoxGroup Finance ManagerCommented:
The code that you have posted doesn't look complete and wants to start from Row 3
0
Roy CoxGroup Finance ManagerCommented:
If you explain exactly what you are copying or preferably provide an example workbook then I know my code will work, I have been using it since 2011!
0
Avinash SinghAuthor Commented:
0
Avinash SinghAuthor Commented:
see the consolidated file
Consolidated-Data.xlsm
0
Avinash SinghAuthor Commented:
Kindly look into it Roy Sir and Plz share the vba  code with me B'coz u r an Expert i got this code from one of my frnd
0
Roy CoxGroup Finance ManagerCommented:
I'll check the code in the morning for you.
1
Roy CoxGroup Finance ManagerCommented:
I've used your data workbooks in a folder and it imports fine.

If you look at the workbook you will see that I highlighted in yellow thelast line of existing data, below that was imported from the other workbooks.
Consolidated-Data-1-.xlsm
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Avinash SinghAuthor Commented:
Thnx Roy Cox sir and everyone for giving ur precious time and great support to this post
0
Roy CoxGroup Finance ManagerCommented:
Pleased to help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.