consolidating data into master sheet

[mithelesh]

Hi all

I am trying to consolidate the data using the macro code.

Below is the link that was posted on chandoo.org

http://chandoo.org/wp/2012/04/09/consolidate-data-from-different-excel-files-vba/

when i run the macro get data.i am getting error .Please help me in this regard.

files:
abc.xlsx(data file)
abcd.xlsx(data file)
Vba-macro...xlsm(is the file where the data is to be consoidated)

Regards
Mithelesh.S

 

[Smallman]

Hi Mithelesh

Can I suggest you go about this a different way?

What I suggest you do is pick a folder and only put the files you want uploaded in that folder. Assuming your data in each file is based on a template then each file has the same structure. If we use your data as a starting point then the following should do the same as the code you picked up from Chandoo. It assumes your data starts in A2 and goes to Col C. You just need to change the path.

If you go with this method then the following code is all that is required.

Option Explicit

Sub MoveData()
Dim sPath As String
Dim sFil As String
Dim owb As Workbook
Dim ws As Worksheet
     
    sPath = Sheet1.[a2] 'Points to the path
    Set ws = Sheet2 'The Master Data Sheet
    sFil = Dir(sPath & "*.xl*") 'Flexible enough to handle all XL file types
   Do While sFil <> "" 'Only Copies Cols A to C, starting In A2.
        Set owb = Workbooks.Open(sPath & sFil)
      Sheets(1).Range("A2", Range("C" & Rows.Count).End(xlUp)).Copy ws.Range("A" & Rows.Count).End(xlUp)(2)
        owb.Close False 'Close no save
        sFil = Dir
    Loop
End Sub

When compared to what you are using it is far more efficient as it does not evaluate a line at a time and selects nothing. It moves all of the data just once.

I have attached the file I used as an example with a fresh button to import the data. Remember put the files in a dedicated folder (remember the back slash) and it should run really well.

Take care

Smallman

 

[mithelesh]

Hi Marcus ,

Thank u for the reply.I tried the code, but when i ran the code the heading is missing.

but the data is consolidated correctly.

Regards
Mithelesh.S

 

[Smallman]

Hi @mithelesh

I made the assumption that you would have the headings in the worksheet already. That is what I do when I run a model like this. You will see in the file I have placed the headings in the sheet before the code runs. The headings are known so they sit in the consolidaiton sheet waiting for the data (that which is unknown) to populate beneath.

Take care

Smallman

 

[Kunal.shah]

Hi Friends,
I am trying to consolidate the data using the macro code.

Below is the link that was posted on chandoo.org

http://chandoo.org/wp/2012/04/09/consolidate-data-from-different-excel-files-vba/

In above link, The last option Copy to Location (Start cell only) is to specify where to paste the copied data and we only need to tell the start cell address, the code will automatically select the next empty cell in that column and then paste the data from that point onwards.

But when i specify Column G or any other cell, code is still selecting the column B & then paste the data from that point onwards.

kindly suggest me how to modify this code for consolidate location would start from Column G or H.

Kindly do the needful.


Regards,

Kunal Shah