Consolidate Data from Multiple Excel Workbooks using VBA
HTML-код
- Опубликовано: 22 июн 2013
- We can consolidate data from multiple Excel workbooks using VBA. This automates the complete process of data consolidation or summation.
The process of consolidating data from different workbooks involves the following steps:
(1) First we write the code for displaying the headers or labels like 'Item' and 'Quantity'. We also write the code to display the item names on the left below the header 'Item'.
(2) Next we write the code to open all the workbooks from which we wish to consolidate
the data
(3) We then write the code to access the first cell where the consolidated data will appear for the quantity
(4) We code to activate the workbook where the data will be consolidated
(5) We write code to access the cells that contain the data to be consolidated for each of the workbooks
(6) We code to close all the open workbooks except our workbook that contains the consolidated data
In this manner we can automate the complete process of data consolidation from multiple workbooks using VBA in MS Excel.
Complete details available at:
www.familycomputerclub.com/con...
For more knowledge read the book Excel 2016 Power Programming with VBA: amzn.to/2kDP35V
If you are from India you can get this book here: amzn.to/2jzJGqU
![Skilla Baby - Misfit (Feat. Polo G) [Official Video]](http://i.ytimg.com/vi/IjsylSnNMyw/mqdefault.jpg)
thatnks alot for sharing this info..very helpful
This is indeed amazing video sir, thanks
Thank you for sharing your experience and tips Dinesh :-)
Thank you for making the video,
Sir my doubt is we are consolidating the data using sum on the basis of Items A,B & C, suppose if the items are jumbled in every file. what will be the code?
Please reply.....
Thanks for the videos, awesome job. But i was wondering, i have a folder on a sharedrive where these files will be kept. I will have multiple files that i want to pull the data from. My co workers will be updating these other workbooks. But what i wanna do is pull their data into one workbook but into different worksheets. Example: excel workbook 1(sheet 1), Main workbook (sheet 1), excel workbook 2 (sheet 1), Main workbook (sheet 2), etc. Each sheet in the main workbook will house the data from each of the other workbooks. I hope it makes since. thanks in advance.
Hi Dinesh - this is exactly what I have been looking for - thanks for sharing. I have created the macro as outlined in your video but when I run it I get an error at "Windows ("Consolidate") .Activate" Error is "Can't execute code in break mode" Any idea how to correct this? Thanks. Gail
it's great
Hello Dinesh, Can you create a if logic on the code of this example video, for example what if workbook B is missing then how do we only run the consolidation on A & C only.
Hi Mr. Takyar I have tried this but got the following error message "compile error: Argument in ParamArray may not be named) and Function:=xlSum highlitted?
I write the same code you guide but it can't work and rise error message:
can not open consolidation sources file '[a.xlsx]sheet1' '
Thanku sir... i have 3 excel excel1,excel2, excel3. With each excel having 3 sheets.. and i have a master file with sheet names 1 2 and 3.. i want to consolidate the data from sheet1 of excel1,e and 3 into master sheet 1 and so on...
Hi sir, Thanks for your videos as am new to vba am feeling very positive after seeing your videos that i can do lot better....
i am having one doubt after seeing your videos i had created 4 userforms for 4 users and 1 master sheet where the userform data to come.
when i tried this in my PC it was working very fine...
but i need all the 4 users to enter their data at the same time at different pc's(connected through LAN) and i need to get master sheet updated at same time...is it possible sir if it is please guide me it will be very helpful for me eagerly waiting for your reply.
Thanks and regards,
Madhusdhanan
Hi sir, we are using time tracker multiple workbooks on daily basis i want macro for consolation based on current date please help me
Hi Mr. Takyar. thank you for this video. an absolute excel novice like me could also understand. I have used a macro that you provided for changing numbers to text as well. My problem however, seems to not get solved. I need to consolidate unique data from the same cell in multiple worksheets into one worksheet and then I want to be able to change data in the single worksheet and have it change in all the refernce cells. is that possible? Would really appreciate any pointers on this. thanks a lot!
You wish to consolidate unique data from the same cell in multiple multiple worksheets into one worksheet. That shouldn't be difficult. Now you want to change the data in the consolidated worksheet and have 'the same value' in all reference worksheets? That is also easy. You just need to reference the proper cells back and forth.
What is the formula to remove last extra delimeters present after the data by excel vba?
Sir I want to consolidate whole date which is A2 to S 25000 as well as sheet wise ex: one excel has 2 sheets data then in consolidation also 3 sheet should appear with all A2 to S25000. Can u help me please
Hello Dinesh Sir,
Thank you very much for the wonderful code, it very useful.
I have a question,
Suppose if n number of files are saved in some directory or folder, How can we consolidate all the files present in that folder (with different names) by just browsing for that folder through vba scripts?
Can we do like this in vba?
It will be great if any one can help me!!
Thanks in Advance.
hi@yogendradilip, you should specify consolidate.xlsm (simply add in the extension of this file, it will work). thanks
Consolidating email attachments
Hi, i receive emails daily from 30 or 40 emails for same subject or email id with the same notepad attachment. Is there any way i can look at consolidating all these?
Sir I am very thankful to you for knowledge sharing videos. I watch most of ur videos. Sir please can help me in masters detail form of invoice I am having problems in the child records some time invoice contain one record and some time 5 records how can I save that In master and traction sheets with VBA. Pls
This Excel VBA training tutorial will help: www.exceltrainingvideos.com/invoice-tracker/
Hello sir, is it possible to consolidate data from one excel workbook with a vba code? (My problem is to get the Consolidate Sources dynamically) I tried many thing and it didn't work. I was wondering if you could help me with this. Hope that you can help me with this.
thank you .... so much .. but i want to know vba code for copy pest value when true
Search www.exceltrainingvideos.com
Hello,
I copied your code exactly as you wrote it, using the same sample files, and yet the code doesn't work.
Windows ("consolidate").Activate - this causes an error for me
ya i does work for me too, is there any help?
Could it be, because he misses the file format? Windows ("consolidate.xlsm").Activate
Do you have a video for this? I do all my estimates on an Excel spreadsheet. The cells that the address goes in is the same for all my saved estimates. I would like to collect the addresses from all my estimates and place it in one file for mailing. Can you help with this?
These links will guide:
www.exceltrainingvideos.com/automate-copy-paste-data-in-non-adjacent-cells-in-sheet1-to-sheet2-with-vba/
www.exceltrainingvideos.com/copy-paste-data-in-excel-with-vba/
www.exceltrainingvideos.com/how-to-add-data-in-non-contiguous-cells-with-vba/
www.exceltrainingvideos.com/copy-non-adjacent-cells-data-from-multiple-workbooks/
For more ideas search www.exceltrainingvideos.com or this RUclips channel.
Dear Dinesh,
Do u have VBA code for sending data from consolidated to respective multiple files? Exactly reversed of this.
Example : suppose I have done any changes in the consolidated file (sheet name 'Final-Data) and I want macro in the consolidated file which helps me to update the same changes in the inputs files.
You can identify inputs file with unique fields of employees id (assume this is in 4th column). Sheet name of inputs file is 'Input-Value' and it is same in all files.
Yes. Run a looping process. This link will help: www.exceltrainingvideos.com/tag/transfer-specific-excel-worksheet-data-range-based-on-criteria/
Or search www.exceltrainingvideos.com
Thanks sir..... I want to consolidate with one header. I mean all workbook has same header with Column A to Column T. Workbook's name not required. Plz help me on this project
Hi, i have 5 workbook each having 3 sheets. all the informaion i should get in to a single file. Could you please help me on this.
Thank you.
Vangipuram R.Bharadwaj See if this link helps: @how-to-consolidate-data-from-multiple-workbooks-in-excel/
Sir
Can we also consolidate multiple sheets from one workbook using VBA codes
Appreciate your reply
These links will help:
www.exceltrainingvideos.com/tag/consolidate-data-from-multiple-worksheets-automatically/
www.exceltrainingvideos.com/how-to-consolidate-data-from-multiple-workbooks-in-excel/
Or search www.exceltrainingvideos.com
How do you get data from a variable workbook (the workbook could have variable names and be in variable locations)?
Search www.exceltrainingvideos.com
Thanks for sharing..... Can you please help me..
I cant find the developer tab , after saving the xlsm macro workbook...'
Please help me...............
+suraj bhandage www.exceltrainingvideos.com/how-to-display-developer-tab-in-microsoft-excel-2010/
Hi is it possible to do loop reverse?
As an example:
let say I have 8 rows of data (A1:A8) which I entered using loop and InputBox function. Now I am trying to do reverse the loop using loop function but I couldn't figure out the code. I want is data from cell A8 To A1 appear on A9 to A16.
Would be greatly appreciated if u can help about it.
This is what i have done for A1:A8
For i = 1 To 8 Step 1
Cells(i, 1).Value = InputBox("Enter SECTION")
Next i
Thanks,
Tek
tek dahal make Step 1 to Step -1, for example:
For i = 8 to 1 step --1
Have a look at this video:
www.exceltrainingvideos.com/delete-blank-rows-from-excel-worksheet-using-reverse-for-next-loop/
Dinesh Kumar Takyar Thanks a lot for respond but still I couldn't do it. Is there any possibility where I can send my work screenshot to you?
Thanks
tek dahal
So this is my working. Now what I am trying to do is: After running this code I will get some data in between column 1-4 and row 7-16.
NOW WHERE TO & HOW TO USE ANOTHER LOOP FUNCTION WHICH WILL REVERSE THE DATA WHICH I HAVE GOT AFTER RUNNING BELOW CODE (i.e. IN COLUMN 1-4 & ROW 7-16) AND REAPPEAR IN COLUMN 1-4 BUT IN ROW 26 TO 16.
In another word I want data from row 16 appear in row 17, data from row 15 appear in row 18, data from row 14 appear in row 19 and so on.
Dim i As Long
For i = 7 To 16 Step 1
Cells(i, 1).Value = InputBox("Enter SECTION")
Cells(i, 2).Value = InputBox("Enter LENGTH IN METRE")
Cells(i, 3).Value = InputBox("Enter GRADE")
Cells(i, 4).Value = InputBox("Enter ROLLING")
Cells(i, 5) = Cells(i, 3) + Cells(i, 4)
If (7
Hi Sir,
Thank you for your help,
I am using your VBA code which you suggest for "Consolidate data form multiple excel workbooks using VBA"
But i am getting Run time error 9, only i am able to paste my header, please check my code-:
Sub ConsolidateData()
On Error Resume Next
Range("A1").Select
ActiveCell.Value = "Sr.No."
Range("B1").Select
ActiveCell.Value = "Chart"
Range("C1").Select
ActiveCell.Value = "Branch"
Range("D1").Select
ActiveCell.Value = "Property Ref. No."
Range("E1").Select
ActiveCell.Value = "Property Address"
Range("F1").Select
ActiveCell.Value = "Invoice No."
Range("G1").Select
ActiveCell.Value = "Invoice Date"
Range("H1").Select
ActiveCell.Value = "Invoice Amount"
Range("I1").Select
ActiveCell.Value = "Process"
Range("J1").Select
ActiveCell.Value = "Date of Submission"
Range("K1").Select
ActiveCell.Value = "Process Data"
Range("L1").Select
ActiveCell.Value = "User Name"
Range("M1").Select
ActiveCell.Value = "Query (Y/N)"
Range("N1").Select
ActiveCell.Value = "Query Date"
Range("O1").Select
ActiveCell.Value = "Remarks"
Range("P1").Select
ActiveCell.Value = "Converted By"
Range("Q1").Select
ActiveCell.Value = "Reviewed"
Range("R1").Select
ActiveCell.Value = "Type of Property"
Range("A2:R").Select
Workbooks.Open Filename:="C:\Users\Neeraj Jha\Desktop\expenditure\a.xlsx"
Workbooks.Open Filename:="C:\Users\Neeraj Jha\Desktop\expenditure\b.xlsx"
Workbooks.Open Filename:="C:\Users\Neeraj Jha\Desktop\expenditure\c.xlsx"
Windows("Consolidate").Activate
Selecion.Consolidate Sources:=Array( _
"'C:\Users\Neeraj Jha\Desktop\expenditure\[a.xlsx]Sheet1'!R2C2:R4C2", _
"'C:\Users\Neeraj Jha\Desktop\expenditure\[b.xlsx]Sheet1'!R2C2:R4C2", _
"'C:\Users\Neeraj Jha\Desktop\expenditure\[c.xlsx]Sheet1'!R2C2:R4C2"), Funcion:=xlSum
Windows("a.xlsx").Activate
ActiveWorkbook.Close
Windows("b.xlsx").Activate
ActiveWorkbook.Close
Windows("c.xlsx").Activate
ActiveWorkbook.Close
End Sub
I am writing the code but getting run time error 9- subscript out of range for Windows("consolidate.xlsm").Activate ....What can be the reason?
Check your worksheet names.
I have only text ,(its means names types) .now how to consolidate the that data, and i have different workbooks have data
What do you wish to achieve by consolidating names? This Excel training video might help: www.exceltrainingvideos.com/get-data-from-multiple-sheets-in-multiple-workbooks-into-master-workbook-with-vba/
Can I compile the data multiple sheet on one work book one bye one because my heder data same please send the code.
These links will help:
www.exceltrainingvideos.com/merge-data-from-worksheets-into-master-worksheet/
www.exceltrainingvideos.com/how-to-transfer-multiple-rows-of-data-from-multiple-workbooks-into-master-workbook-with-vba/
the coding provided may not be working for the current version of excel. I am using excel 2016. I tried running the vba and it is giving me an error at this junction of the vba: "'...\Tally\c.xlsx'!R6C2:R58C2"), Function:=xlSum with an error: run-time error 438?
Check your path and code again: www.familycomputerclub.com/consolidate-data-from-multiple-excel-workbooks-using-vba.html
@@Exceltrainingvideos thanks Dinesh, the path appears to be correct (no error)...the error appears to be with the the line that included 'function\;=xlsum\'...each line appears to be error free until it get to the 'function' line...i am currently using excel 2016? is there a new sum function for total with 2016? perhaps WorksheetFunction.Sum("myRange")
I am receiving error for the same codes used. the code works till opening of all workbooks then it gives error. I am unable to find out,could you help me
Yes, you are right. Same error is at my side
Hi Sir, Could you please help me on VBA code for below scenario..I have a folder which is having multiple files, in that few files having 15 columns of headers and few of files having more than 32 columns of headers,, so if file has 15 columns of headers need to copy as it is and paste into consolidated file, if file has 32 columns of headers need select required columns (up to 15 columns which are having same in 15 columns file but header name will be slight different ..example: if header name in 15 columns of the file has "Subject" but in different file header will be "SubjectID")which was align in between the headers, and paste into same consolidated file next available empty rows,, Thank you so much in advance...
You can use a text function like 'LEFT' to extract the common matter 'Subject' and then do the needful using dictionary or advanced filter.
SIR I DOWNLOD 72 EXCEL FILE
THAN MAKE NEW FILE ALL FILE COPY AND PASTE ONE BY ONE IN NEW FILE .
THESE PROCESS IS VERY LENTHY. IS IT POSSIBLE BY VB DONE THIS WORK
+Ust Igrs www.exceltrainingvideos.com/consolidate-excel-worksheet-data-using-copy-autofill/
www.exceltrainingvideos.com/how-to-consolidate-data-from-multiple-excel-workbooks-using-vba/
www.exceltrainingvideos.com/tag/get-data-from-multiple-sheets-into-master-workbook-automatically/
How come it is so blurry??
Hi Sir,
Thanks a lot for this Video tutorial.
I've a requirement. I have 10 Excel files and each holds two sheets in it.
So i want to merge all the excel files including their sheets into new excel file(Master Excel) which has same two sheets in it.
The below code helps me to fetch all the data from only one sheet of all the excel file and merge into master excel file.
Sub simpleXlsMerger()
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")
Set dirObj = mergeObj.Getfolder("D:\New folder")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End Sub
I kindly request you to help me to get the result when multiple excels has multiple sheets to be get merged into master excel.
This is not a dynamic range you are using. It will not work to other sheets
What should be done?
Do you have a video for this? I do all my estimates on an Excel spreadsheet. The cells that the address goes in is the same for all my saved estimates. I would like to collect the addresses from all my estimates and place it in one file for mailing. Can you help with this?
See reply above.