Consolidating 3 tables (from 3 different workbooks) into a single table in a 4th Workbook

[Kaushik Joshi]

Hello folks,

beginning VBA with a seemingly simple issue!

Trying to consolidate 3 sheets into a one. Have 3 tables in 3 sheets. Want to make it into one table....meaning table1 + table 2 + table 3...one below the other.

Solution should be such that users should be able to update 3 tables and the macro should fetch updated data any time it is run and create a Consolidated table...

Giving 3 sample files with 3 tables containing a 5 records in each table.


can someone give a hint or a reference...

 

[Chihiro]

Since all 3 tables are identical in structure... why not us MS Query to UNION ALL?

You can record Macro and use that for basis of your code.

 

[PCosta87]

Hi @Kaushik Joshi

In new workbook add a module with the following code:

Sub Macro()

    Dim lrow1, lrow2, lrow3 As Integer

    lrow1 = Workbooks("Khushboo_sheet 1").Sheets(1).Columns("A").Cells(Rows.Count).End(xlUp).Row
    lrow2 = Workbooks("Preeti_sheet 2").Sheets(1).Columns("A").Cells(Rows.Count).End(xlUp).Row
    lrow3 = Workbooks("Shraddha_Sheet 3").Sheets(1).Columns("A").Cells(Rows.Count).End(xlUp).Row

    ActiveSheet.Cells.Clear
    Workbooks("Khushboo_sheet 1").Sheets(1).Range("A1:R" & lrow1).Copy ActiveSheet.Range("A1")
    Workbooks("Preeti_sheet 2").Sheets(1).Range("A2:R" & lrow2).Copy ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    Workbooks("Shraddha_Sheet 3").Sheets(1).Range("A2:R" & lrow3).Copy ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

End Sub

Untested but it should work... just make sure all uploaded workbooks are open before running the code.

Hope this is what you were looking for

 

[Kaushik Joshi]

Hi Chihiro
I really need to know how to run MS Query in Excel 10, I think I need to download it as an add on ??
will surely make attempts to read , learn more on this....thanks for this suggestion..

Hi PCosta,
Thanks for the Macro script...this is really magic for me....
Will you please explain in short the logic you applied...I mean how does it recognise end of data in a sheet? and how does it actually work??...
I see that live data is in a single file with 3 different sheets.
Will have to tweak the macro a bit to create a 3rd sheet....will try this on my own by using some very basic VBA I have learnt. Also I would like to create a table out of the consolidated data for analysis with pivots and slicers etc. Do you suggest I create a table each time I consolidate using your Macro or should I create a blank table with a large number of rows and then run the consolidate macro.

I am grateful and thanks for this great help on my project...

Kaushik

 

[Chihiro]

MS Query comes standard with Excel (at least 2003 on ward).

Go to Data ribbon/menu, and you should see "Get External Data". From there, go to "From Other Sources" and you will see "From Microsoft Query".

 

[Marc L]

Translation from a 2003 local version : something like
Data, External Data, Create a query (or maybe Create a request) …
​

 

[PCosta87]

...Hi PCosta,
Thanks for the Macro script...this is really magic for me....
Will you please explain in short the logic you applied...I mean how does it recognise end of data in a sheet? and how does it actually work??...
I see that live data is in a single file with 3 different sheets.
Will have to tweak the macro a bit to create a 3rd sheet....will try this on my own by using some very basic VBA I have learnt. Also I would like to create a table out of the consolidated data for analysis with pivots and slicers etc. Do you suggest I create a table each time I consolidate using your Macro or should I create a blank table with a large number of rows and then run the consolidate macro.

I am grateful and thanks for this great help on my project...

Kaushik

Hi,

First of all, you are welcome

Now, about the end of data... the code identifies the last cell in column A which is "ActiveSheet.Cells(Rows.Count, 1)" where "rows.count" serves as the line number and "1" as column A, so it reads something like: cell(number of rows, column A). Then it goes up from there looking for the last non blank cell ".End(xlup)" and offsets it by one line to position it self in the first blank cell "offset(1,0)"
This is just one way of doing it... you could go the other way around, for instance, going from A1 to the last non blank and offsetting it by (1,0).
It does this each time it pastes the values from each of the other sheets so it always goes to the row below the previously pasted data.

About the second question, I would suggest converting data to a Table and creating the pivot from it... that way, when you update the data, pivot table will already have the correct range since it is not referring to a specific range but to a named range (Table name) which can expand.
If you need any help with that just let me know

 

[Kaushik Joshi]

@Pcosta,
Grateful and thanks a lot for both answers ! will try to work further on my own...learning VBA and successfully implement it is an uphill climb, if i stumble will ask for a help, but climb i will !!
Thanks again for the start !!
@ Chihiro - explored data connection from another Excel file.....worked for one to one table in separate workbooks, need to see how to make it work 3 to 1!

 

[PCosta87]

Keep at it
Have a nice day

 

[Kaushik Joshi]

Currently we are getting data from 3 sheets belonging to 3 different files that need to be kept open before running the macro. Can my Macro follow a path to a workbook file that is NOT OPEN and fetch data if 3 sheets located in that workbook file.
Tried achieving by changing code, but failed...or keeping it open and working is a better idea...

Also if is it advisable to prepare a table in Consolidation file and then overwrite pulled data on to the table.

 

[PCosta87]

Currently we are getting data from 3 sheets belonging to 3 different files that need to be kept open before running the macro. Can my Macro follow a path to a workbook file that is NOT OPEN and fetch data if 3 sheets located in that workbook file.
Tried achieving by changing code, but failed...or keeping it open and working is a better idea...

Also if is it advisable to prepare a table in Consolidation file and then overwrite pulled data on to the table.

Yes, you can do that... although macro will need to open the file and close it after copying the data. I don't think it can be done without opening the file but it is irrelevant as the macro will handle all operations.

If you wish, provide sample file with the 3 sheets and the path to the file and I can help with the code.

As for the consolidation file, you can prepare the table beforehand and just overwrite if you prefer... code will have to be slightly changed but it should be simple enough

 

[Marc L]

Hi !

Could be done also using ADODB if worksheet of closed workbook
is well structured … (see samples within threads of this forum)
​

 

[Chihiro]

For 3 to 1. You'd do "UNION" of each query.

For example: If I had all 3 files in "C:\Test\Consolidate\" folder.

SELECT *
FROM `C:\Test\Consolidate\Khushboo_sheet 1.xlsx`.`Sheet1$` `Sheet1$`
UNION ALL
SELECT *
FROM `C:\Test\Consolidate\Preeti_sheet 2.xlsx`.`Sheet1$` `Sheet1$`
UNION ALL
SELECT *
FROM `C:\Test\Consolidate\Shraddha_Sheet 3.xlsx`.`Sheet1$` `Sheet1$`

You'd edit this in "Connection"->"Property". In "Definition" tab, in Command Text pane.

 

[Kaushik Joshi]

@ Marc, Chihiro - Thanks for those interesting ideas, will try those...
@Costa -
Attaching file KPIs.xlsx where we have all 3 sheets in a single file. would like help on Macro that will open this file, copy data from each of these sheets and put it in a single Sheet in different file inside an empty table for further analysis. Idea is to get refreshed data from 3 sheets each time the macro is run, pretty much the way your first Macro worked !! Path where the KPIs.xlsx is located is "Z:\KPIs.xlsx"
Thanks for helping, really appreciate!

 

[PCosta87]

Hi

Sorry for the late response but I don't check the forum at weekends...

So, for your request, below you can find two macros:
Macro - copies and pastes as values, meaning you can have a formatted table in the destination workbook and it will keep that... Note that this also copies the headers but can be easily altered to copy only the contents if you so wish;

Macro1 - Does basically the same but also pastes formats, which means it will create a table similar to the originals and overwrite everything in the destination

Sub Macro()
   
    Application.ScreenUpdating = False

    Workbooks.Open "Z:KPIs.xlsx"
   
    Dim lrow1, lrow2, lrow3 As Integer
    Dim ThisWrkbk As Workbook
   
    Set ThisWrkbk = ThisWorkbook
   
    With Workbooks("KPIs.xlsx")
        lrow1 = .Sheets("Khushboo").Columns("A").Cells(Rows.Count).End(xlUp).Row
        lrow2 = .Sheets("Preeti").Columns("A").Cells(Rows.Count).End(xlUp).Row
        lrow3 = .Sheets("Shraddha").Columns("A").Cells(Rows.Count).End(xlUp).Row
   
        ThisWrkbk.Sheets(1).Cells.ClearContents
        .Sheets("Khushboo").Range("A1:R" & lrow1).Copy
            ThisWrkbk.ActiveSheet.Range("A1").PasteSpecial xlPasteValues
        .Sheets("Preeti").Range("A2:R" & lrow2).Copy
            ThisWrkbk.ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        .Sheets("Shraddha").Range("A2:R" & lrow3).Copy
            ThisWrkbk.ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
   
        Application.DisplayAlerts = False
        Workbooks("KPIs.xlsx").Close savechanges = False
        Application.DisplayAlerts = True
       
    End With

    Application.ScreenUpdating = True
   
End Sub

Sub Macro1()

    Application.ScreenUpdating = False

    Workbooks.Open "Z:KPIs.xlsx"
   
    Dim lrow1, lrow2, lrow3 As Integer
    Dim ThisWrkbk As Workbook
   
    Set ThisWrkbk = ThisWorkbook
   
    With Workbooks("KPIs.xlsx")
        lrow1 = .Sheets("Khushboo").Columns("A").Cells(Rows.Count).End(xlUp).Row
        lrow2 = .Sheets("Preeti").Columns("A").Cells(Rows.Count).End(xlUp).Row
        lrow3 = .Sheets("Shraddha").Columns("A").Cells(Rows.Count).End(xlUp).Row
   
        ThisWrkbk.Sheets(1).Cells.Clear
        .Sheets("Khushboo").Range("A1:R" & lrow1).Copy ThisWrkbk.ActiveSheet.Range("A1")
        .Sheets("Preeti").Range("A2:R" & lrow2).Copy ThisWrkbk.ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        .Sheets("Shraddha").Range("A2:R" & lrow3).Copy ThisWrkbk.ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
   
        Application.DisplayAlerts = False
        Workbooks("KPIs.xlsx").Close savechanges = False
        Application.DisplayAlerts = True

    End With

    Application.ScreenUpdating = True

End Sub

Let me know if there anything else I can help with

 

[Kaushik Joshi]

Hi,
thank you for help! You have been very kind in responding to all my doubts so quickly, absolutely no issues this time....... i prefer to spend some time learning on a Weekend, but expect no responses !!
i tested the first "Macro" - and prefer this one because I want my original formatted table to be preserved as a lot of pivot & slicers reports would depend on this table. the reason I would want to avoid 2nd one - "Macro1" - is that it might create or necessitate me to create another table, which would jeopardise my already created Pivot & slicer reports. for eg. If I have created these reports on Table 1 and If I or the macro create another table 2, then my reports linked to table 1 are rendered useless. Hence I prefer the first one.....
But, what I noticed in the first one "Macro" is that if I create a table 1, it gets overwritten by values, though the formats are preserved. i.e. My Table 1 disappears.......should I live with creating it again, in order to run Pivot/slicer reports??
Thanks again & grateful for your help!

 

[PCosta87]

I believe we can work with that...

Please upload file with desired table (file where data will be pasted) so that i can incorporate it in the code, making sure to keep the table after pasting values.

Thanks

 

[Kaushik Joshi]

Re-creating the table is not working either as Table 1 goes in cache and I have to compulsorily create Table 2 after re-running the Macro..... which means again i have to go back into pivots and change source.....I cannot keep doing this in fact, its not a solution. Would there be a possibility where the macro updates and preserves Table 1 if i have created it? and reports dependent on this table remain intact and refreshable....

 

[Kaushik Joshi]

here is the file I am trying to work with. Currently table 2 is operational and Table 1 has got over written and I presume its in cache..... if you create a code i will use it in a fresh file and hopefully tweak it to suit table 1 in fresh file....
note that i have made some changes to the original code ....some sheet names and file name are different than originally provided....

 

[PCosta87]

here is the file I am trying to work with. Currently table 2 is operational and Table 1 has got over written and I presume its in cache..... if you create a code i will use it in a fresh file and hopefully tweak it to suit table 1 in fresh file....
note that i have made some changes to the original code ....some sheet names and file name are different than originally provided....

Hi,

Please see attached... I also added some code to refresh pivot table so there's no need to do it manually anymore.
Replace previous code with:

Sub Macro()
 
    Application.ScreenUpdating = False

    Workbooks.Open "Z:Tracking Sheet - Shared Delivery.xlsx"

    Dim lrow, lrow1, lrow2, lrow3 As Integer
    Dim ThisWrkbk As Workbook

    Set ThisWrkbk = ThisWorkbook
    lrow = ThisWrkbk.Sheets(1).Columns("A").Cells(Rows.Count).End(xlUp).Row

    With Workbooks("Tracking Sheet - Shared Delivery.xlsx")
        lrow1 = .Sheets("Khushbu").Columns("A").Cells(Rows.Count).End(xlUp).Row
        lrow2 = .Sheets("Preethi").Columns("A").Cells(Rows.Count).End(xlUp).Row
        lrow3 = .Sheets("Shraddha").Columns("A").Cells(Rows.Count).End(xlUp).Row

        ThisWrkbk.Sheets(1).Rows("2:" & lrow).Delete shift:=xlUp
        .Sheets("Khushbu").Range("A2:T" & lrow1).Copy
            ThisWrkbk.ActiveSheet.Range("A2").PasteSpecial xlPasteValues
        .Sheets("Preethi").Range("A2:T" & lrow2).Copy
            ThisWrkbk.ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        .Sheets("Shraddha").Range("A2:T" & lrow3).Copy
            ThisWrkbk.ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

        Application.DisplayAlerts = False
        Workbooks("Tracking Sheet - Shared Delivery.xlsx").Close savechanges = False
        Application.DisplayAlerts = True

    End With

    ThisWrkbk.Sheets("Analysis").PivotTables("PivotTable1").PivotCache.Refresh

    Application.ScreenUpdating = True
 
End Sub

Hope this works as intended

 

[Kaushik Joshi]

Hi,
Sorry for delay in checking and responding...
Indeed it works as intended.
Did another check. Converted source data into a table and then running the macro. It worked fine. Means I can put some helper columns in source data table and just extend the columns in consolidation table and code.

This has helped finally beginning with VBA !!!!.....will pour over the code line by line to know how exactly it worked ..... guess its much different than plain macro recording with efficient use of relative reference button.
Remember my early struggle with learning French !!!! doesn't happen overnight....could you suggest some good resources to speeden up??

Thanks and grateful again !!
Have a great day!

 

[PCosta87]

Hi,

I'm glad it helped

The way I see it, there are many ways to learn VBA...
Even without leaving the site you can start by visiting:
http://chandoo.org/wp/excel-vba/

Other site I found useful at some point was:
http://www.excel-pratique.com/en/vba.php

However, for me at least, real progress came when actually trying to solve day to day problems through VBA (many times things that could easily be done without resorting to macros), taking the time to search online for bits and pieces to integrate into whatever I was trying to do, and increasingly trying to automate more complex operations.

Trying to help others in this or any other forum is also a great way to improve rapidly as you will get much more variety of problems and indeed solutions.

Have a nice day

 

[Kaushik Joshi]

Oh yes indeed, all the needed resources are right below my nose at Chandoo's VBA section !!
Also I do agree that solving daily problems beginning from simple ones to automating more complex ones is a great way to learn....!! I only wanted to hear it again from someone else!!
and thanks for that useful site address which gives me chance to practice my French with VBA !! Passe une excellente Journee !!!

 

[Kaushik Joshi]

@PCosta87

Hi,

Need some help on same topic of consolidation.
I modified the macro you made.
Originally we consolidated 3 sheets from same file into 1 consolidated file.
Now i want to implement consolidation from 3 sheets existing in 3 different files into 1 consolidated file.
I am attaching 1 consolidated file and 3 data files to serve as a simple model of what I want to achieve.
I have attempted the macro but following are some problems I am facing:
a) All 3 data files remain open
b) first row of Consolidated which is supposed to be heading gets over written
c) file does not show up in the module, shows up as a macro
d) plus I get an error "Run time error 9 - Subscript out of range", when debugged it comes and stops at closing Data file no. 1

I just played with your code, but could succeed only so little ......but with so much more mess! Requesting your help.....at your convenience please.

Once this is sorted I want to run similar macro on consolidating two more sheets named "DEL" and "TRF" from Location sheets into the same consolidated file Add_Del_Trf.xlsx.

Hope you can help,
Sincerely,
KJ

 

[PCosta87]

@PCosta87

Hi,

Need some help on same topic of consolidation.
I modified the macro you made.
Originally we consolidated 3 sheets from same file into 1 consolidated file.
Now i want to implement consolidation from 3 sheets existing in 3 different files into 1 consolidated file.
I am attaching 1 consolidated file and 3 data files to serve as a simple model of what I want to achieve.
I have attempted the macro but following are some problems I am facing:
a) All 3 data files remain open
b) first row of Consolidated which is supposed to be heading gets over written
c) file does not show up in the module, shows up as a macro
d) plus I get an error "Run time error 9 - Subscript out of range", when debugged it comes and stops at closing Data file no. 1

I just played with your code, but could succeed only so little ......but with so much more mess! Requesting your help.....at your convenience please.

Once this is sorted I want to run similar macro on consolidating two more sheets named "DEL" and "TRF" from Location sheets into the same consolidated file Add_Del_Trf.xlsx.

Hope you can help,
Sincerely,
KJ

Hi,

You were almost there!

Try it like this:

Sub Add()

    Application.ScreenUpdating = False
    'Turn screen updating off to speed up your macro code.
    'You wont be able to see what the macro is doing, but it will run faster.
    'Remember to set the ScreenUpdating property back to True when your macro ends.
  
    For i = 1 To 3
        Workbooks.Open "C:\Users\K6246948\Desktop\DEL-2016-06\GNO-HRIS Reports Automated\Metrics-Vish\HC_Con\Loc" & i & ".xlsx"
    Next i

    Dim lrow, lrow1, lrow2, lrow3 As Integer
    'integer is a whole number, a number which is not a fraction
    Dim ThisWrkbk As Workbook

    Set ThisWrkbk = ThisWorkbook
    lrow = ThisWrkbk.Sheets("Add").Cells(Rows.Count, "A").End(xlUp).Row
    ThisWrkbk.Sheets("Add").Cells.ClearContents

    With Workbooks("Loc1.xlsx")
        lrow1 = .Sheets("Add").Cells(Rows.Count, "A").End(xlUp).Row
        .Sheets("Add").Range("A1:F" & lrow1).Copy ThisWrkbk.ActiveSheet.Range("A1")
        .Close savechanges = False
    End With
  
    With Workbooks("Loc2.xlsx")
        lrow2 = .Sheets("Add").Columns("A").Cells(Rows.Count).End(xlUp).Row
        .Sheets("Add").Range("A2:F" & lrow2).Copy ThisWrkbk.ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        .Close savechanges = False
    End With

    With Workbooks("Loc3.xlsx")
        lrow3 = .Sheets("Add").Columns("A").Cells(Rows.Count).End(xlUp).Row
        .Sheets("Add").Range("A2:F" & lrow3).Copy ThisWrkbk.ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        .Close savechanges = False
    End With

    'ThisWrkbk.Sheets("Analysis").PivotTables("PivotTable1").PivotCache.Refresh

    Application.ScreenUpdating = True

End Sub

Sub Del()

    Application.ScreenUpdating = False
    'Turn screen updating off to speed up your macro code.
    'You wont be able to see what the macro is doing, but it will run faster.
    'Remember to set the ScreenUpdating property back to True when your macro ends.
  
    For i = 1 To 3
        Workbooks.Open "C:\Users\K6246948\Desktop\DEL-2016-06\GNO-HRIS Reports Automated\Metrics-Vish\HC_Con" & i & ".xlsx"
    Next i

    Dim lrow, lrow1, lrow2, lrow3 As Integer
    'integer is a whole number, a number which is not a fraction
    Dim ThisWrkbk As Workbook

    Set ThisWrkbk = ThisWorkbook
    lrow = ThisWrkbk.Sheets("Del").Cells(Rows.Count, "A").End(xlUp).Row
    ThisWrkbk.Sheets("Del").Cells.ClearContents

    With Workbooks("Loc1.xlsx")
        lrow1 = .Sheets("Del").Cells(Rows.Count, "A").End(xlUp).Row
        .Sheets("Del").Range("A1:H" & lrow1).Copy ThisWrkbk.ActiveSheet.Range("A1")
        .Close savechanges = False
    End With
  
    With Workbooks("Loc2.xlsx")
        lrow2 = .Sheets("Del").Columns("A").Cells(Rows.Count).End(xlUp).Row
        .Sheets("Del").Range("A2:H" & lrow2).Copy ThisWrkbk.ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        .Close savechanges = False
    End With

    With Workbooks("Loc3.xlsx")
        lrow3 = .Sheets("Del").Columns("A").Cells(Rows.Count).End(xlUp).Row
        .Sheets("Del").Range("A2:H" & lrow3).Copy ThisWrkbk.ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        .Close savechanges = False
    End With

    'ThisWrkbk.Sheets("Analysis").PivotTables("PivotTable1").PivotCache.Refresh

    Application.ScreenUpdating = True

End Sub

I coded for the "Add" sheet as well as for the "Del". I will leave the "Trf" to you as I believe you will now be able to do it by yourself

On a side note, I used a loop to open all 3 "Loc" files since they were named sequentially (Loc1, Loc2, Loc3)... if you have the files in different locations or if the names are totally different from one another, just keep the code for opening you had earlier.

To run the macros simply press the "Consolidate" button on each sheet and it should work as intended.
Also remember that, since we are using "Activesheet" as the destination for the copy/paste process, you must have the correct sheet active before running the code (if you are running it from Alt+F8 or from the VBA window)... not a problem if you are using the buttons on the actual sheet!

Please refer to attachment.

If you have any trouble don't hesitate to ask.