VBA to pull data from multiple excel sheets and multiple workbooks to a different consolidated worksheet

[lcaindoy]

Hi guys,

Hope you can help me out, I'm a newbie here but I'm always looking for ways to reduce amount of time in getting data.

What I'm try to do right now is to pull worksheet data with same worksheet name from different workbooks and consolidate into a different workbook one a worksheet.

 

[Fazza]

welcome

I googled for your thread title: [h=2]VBA to pull data from multiple excel sheets and multiple workbooks to a different consolidated worksheet[/h]
and these were the first few hits
https://www.extendoffice.com/documents/excel/5017-excel-collect-data-from-multiple-sheets.html
https://www.extendoffice.com/documents/excel/456-combine-multiple-workbooks.html
https://stackoverflow.com/questions...tiple-workbooks-with-added-refresh-capability
https://www.youtube.com/watch?v=bRAtOnKYdjI
https://excel.tips.net/T007425_Combining_Worksheets_from_Many_Workbooks.html
https://support.office.com/en-us/ar...rksheets-007ce8f4-2fae-4fea-9ee5-a0b2c9e36d9b
https://chandoo.org/wp/consolidate-data-from-different-excel-files-vba/
https://www.accountingweb.com/technology/excel/combining-multiple-worksheets-in-any-version-of-excel
https://trumpexcel.com/combine-multiple-workbooks-one-excel-workbooks/

if none of the links do what you want, please provide further explanation. thanks

 

[Fazza]

also some good stuff at Ron de Bruin's site, https://www.rondebruin.nl/win/section3.htm

 

[lcaindoy]

Hi Fazza,

Input:
The following are the info
1. There are 7 excel workbooks
2. There are worksheets inside each workbook with name "Master"
3. The data that I'm looking to consolidate is in the "Master" worksheets
4. There is one file path for the workbooks to be consolidated
Desired Output:
1. I would like a macro that pulls all the data of "Master" worksheet from all of closed workbooks saved in one file path.
2. Hoping that this can be consolidated in one work sheet of another workbook.
3. After getting from workbook1, "Master" worksheet and pasting in the consolidating file, next it would pull the data from workbook 2 "Master" worksheet and paste after the last row of the retrieved data in workbook1. Until all 7 workbook consolidates into 1 spreadsheet.
I don't know if my wordings is understandable.
I have checked the below suggestions but none of it works for me or I'm having a problem during its execution, it prompts an error and I don't know how to fix it.
https://www.extendoffice.com/documents/excel/5017-excel-collect-data-from-multiple-sheets.html - this I think works for data which is in the same workbook. Also I'm trying the code if data is within same workbook, its not collating my data.
https://www.extendoffice.com/documents/excel/456-combine-multiple-workbooks.html - this doesn't work and it does not solve my problem even if it works.
https://stackoverflow.com/questions...tiple-workbooks-with-added-refresh-capability - same as above.
https://www.youtube.com/watch?v=bRAtOnKYdjI - sucks but we are not allowed to use youtube in the office
https://excel.tips.net/T007425_Combining_Worksheets_from_Many_Workbooks.html - this gets all the worksheet into one spreadsheet. What I want is to get merge all the data from those worksheet from all workbooks.https://support.office.com/en-us/ar...rksheets-007ce8f4-2fae-4fea-9ee5-a0b2c9e36d9b - works for data which is in the same workbook.
https://chandoo.org/wp/consolidate-data-from-different-excel-files-vba/ - It has syntax error when I copied in the vba module
https://www.accountingweb.com/technology/excel/combining-multiple-worksheets-in-any-version-of-excel - it needs to download their tool which is not allowed in our company to install add in tools
https://trumpexcel.com/combine-multiple-workbooks-one-excel-workbooks/ - it does not solve my problem. Also the suggestion is not working.

 

[lcaindoy]

welcome

I googled for your thread title: VBA to pull data from multiple excel sheets and multiple workbooks to a different consolidated worksheet


and these were the first few hits
https://www.extendoffice.com/documents/excel/5017-excel-collect-data-from-multiple-sheets.html
https://www.extendoffice.com/documents/excel/456-combine-multiple-workbooks.html
https://stackoverflow.com/questions...tiple-workbooks-with-added-refresh-capability
https://www.youtube.com/watch?v=bRAtOnKYdjI
https://excel.tips.net/T007425_Combining_Worksheets_from_Many_Workbooks.html
https://support.office.com/en-us/ar...rksheets-007ce8f4-2fae-4fea-9ee5-a0b2c9e36d9b
https://chandoo.org/wp/consolidate-data-from-different-excel-files-vba/
https://www.accountingweb.com/technology/excel/combining-multiple-worksheets-in-any-version-of-excel
https://trumpexcel.com/combine-multiple-workbooks-one-excel-workbooks/

if none of the links do what you want, please provide further explanation. thanks

Hi Fazza,

Input:
The following are the info
1. There are 7 excel workbooks
2. There are worksheets inside each workbook with name "Master"
3. The data that I'm looking to consolidate is in the "Master" worksheets
4. There is one file path for the workbooks to be consolidated
Desired Output:
1. I would like a macro that pulls all the data of "Master" worksheet from all of closed workbooks saved in one file path.
2. Hoping that this can be consolidated in one work sheet of another workbook.
3. After getting from workbook1, "Master" worksheet and pasting in the consolidating file, next it would pull the data from workbook 2 "Master" worksheet and paste after the last row of the retrieved data in workbook1. Until all 7 workbook consolidates into 1 spreadsheet.
I don't know if my wordings is understandable.
I have checked the below suggestions but none of it works for me or I'm having a problem during its execution, it prompts an error and I don't know how to fix it.
https://www.extendoffice.com/documents/excel/5017-excel-collect-data-from-multiple-sheets.html - this I think works for data which is in the same workbook. Also I'm trying the code if data is within same workbook, its not collating my data.
https://www.extendoffice.com/documents/excel/456-combine-multiple-workbooks.html - this doesn't work and it does not solve my problem even if it works.
https://stackoverflow.com/questions...tiple-workbooks-with-added-refresh-capability - same as above.
https://www.youtube.com/watch?v=bRAtOnKYdjI - sucks but we are not allowed to use youtube in the office
https://excel.tips.net/T007425_Combining_Worksheets_from_Many_Workbooks.html - this gets all the worksheet into one spreadsheet. What I want is to get merge all the data from those worksheet from all workbooks.https://support.office.com/en-us/ar...rksheets-007ce8f4-2fae-4fea-9ee5-a0b2c9e36d9b - works for data which is in the same workbook.
https://chandoo.org/wp/consolidate-data-from-different-excel-files-vba/ - It has syntax error when I copied in the vba module
https://www.accountingweb.com/technology/excel/combining-multiple-worksheets-in-any-version-of-excel - it needs to download their tool which is not allowed in our company to install add in tools
https://trumpexcel.com/combine-multiple-workbooks-one-excel-workbooks/ - it does not solve my problem. Also the suggestion is not working.

 

[lcaindoy]

also some good stuff at Ron de Bruin's site, https://www.rondebruin.nl/win/section3.htm

Hi Fazza,

I'm trying to follow the instruction in the above link but I can't. I am a novice in this field but I'm still trying to make things work.

 

[Fazza]

you've covered some ground

please explain how the data is on these "master"worksheets. I'm hoping/expecting it is like a database table. So headers in row 1 and data underneath. No blank rows. No merged cells.

I guess this is not a one off task. and that makes me ask do the names of the 7 source workbooks change? or they might be the same each time (such as each week, each month) when the consolidation is done.

If the data is set up like a data table on each source file & the file names don't change, an option might be to do a non-VBA solution, btw. maybe

 

[Fazza]

I had a look at the one that gave you a syntax error, https://chandoo.org/wp/consolidate-data-from-different-excel-files-vba/

It works fine for me. Please can you re-look at it & advise where the error is?
It may be a simple set up issue - like the worksheet name?

I have modified it a tiny bit to suit what I guess you may have. Description follows, based on a download from Chandoo's site. The sample file is set up to include the names of the cells to be copied from each of the files. Such as A2 to G10 for the first record. I'm guessing this would be better if the code worked out the cells to copy. So I've edited the code. The bit I've changed is within the "========" marks. Also I've set within the code the name of the worksheet you're copying from. This would be better in the table that defines what is happening but I've taken the expedient (lazy really) option & simply hard coded it into the code - rather than modify both the table and the code.

        Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=True        Set dataWB = ActiveWorkbook
        
'=================
        'Range(strCopyRange).Select 'I've edited this row & replaced it by the four following
         dataWB.Worksheets("Master").Activate
        With Range("A1").CurrentRegion
            .Offset(1).Resize(.Rows.Count - 1).Select
        End With
'=================
        Selection.Copy
        
        currentWB.Activate
        Sheets(strWhereToCopy).Select
        lastRow = LastRowInOneColumn(strStartCellColName)
        Cells(lastRow + 1, 1).Select

To summarise,
1. download the file from Chandoo's site
2. fill in the table to suit your file names & worksheet names
3. edit the code per above

If it doesn't do what you want, please advise specifics. thanks, Fazza

 

[Fazza]

PS.

With the edit I've made to the code, you can leave empty cells D2:Ezz in the set up table. The code automatically finds the data from the "Master" worksheets rows 2 to the end.
The last column of the table, CopyToLocation, you can put $a$2 for each of your seven files. The codes has been set up to find the next row after the current last one each time.

 

[lcaindoy]

PS.

With the edit I've made to the code, you can leave empty cells D2:Ezz in the set up table. The code automatically finds the data from the "Master" worksheets rows 2 to the end.
The last column of the table, CopyToLocation, you can put $a$2 for each of your seven files. The codes has been set up to find the next row after the current last one each time.

If you could check the below link
https://www.mrexcel.com/forum/excel...orting-data-multiple-wordbooks-one-sheet.html

I think I have same problem but mine doesn't include any range instead I want to copy all data