VBA to Consolidate data from Multiple Sheets - Excel VBA Example by Exceldestination
HTML-код
- Опубликовано: 23 июл 2024
- This excel vba tutorial explains, VBA Macro to consolidate data from multiple sheets. MS Excel has excellent capability with VBA to transfer data quickly from multiple sheets. We can use for loop with if condition in vba to develop vba code for data consolidation.
I entered above VBA code, it worked PERFECT! Now it is very easy to import data from different worksheets! Thank You, Excel Destination! More such videos pls!
After I finished my course for basics VBA and did some programming here and there in Excel, I wanted to go further with practical, daily-life examples that I could come up with, and your videos are 100% like that, I am learning a lot with your videos.
Thanks a lot :)
That is stunning. Thank you so much for sharing this truly useful video. 👏 🌟 🌟 🌟 🌟
Thank you very much for this great video and this simple explanation
This is great, thanks Excel Destination! :)
Thank you, you made it easy to follow. My understanding is the codes copies each row and not a block of cells.
Thanks a lot for your ideas!
Awesome. Thank you!!
Thank you for your tutorial, but I noticed that if we alter data on sheets (“aa.-dd”) or update the details, it will add to Mastersheet as a new count. Hope you can help me. Thank you.
If i need to extravt data of specific values or word remarks
What will be the code
Hi Your video very useful, however i have one questions, how to ignore the hide sheets while consolidating the sheets. while consolidating the date it picks the hide data also
Can we transpose the data in master sheet?
it is really good make my works super fast. only keep asked me to make a copy for every worksheets
but sirs, why it keep asking me to save a copy.
Awesome code
How to exclude more sheets instead of only the mastersheet is excluded?
Can i used this code if the header of columns is different
Thank you, I was able to get this to work. One quick question, how do I exclude to worksheets?
you can use if worksheets("sheetname")"sheetname" and then start your code.
The tutorial looks good and easy to implement. However there is an issue here with my Micro and also there is an additional requirement.
The issue is the selection copy is working fine. However the paste functionality isn't working. When i troubleshoot the VBA with alt+f8 I see the respective rows of each sheet being selected and copied in the clipboard. However it is not being pasted in the mastersheet
About the requirement- alongwith the data in each of the sheets I also want to copy the name of the sheets from which these data is collated into the mastersheet. Is there any variable that stores this value? How can I attach it with the output
you can send me email with your file at EXCELDESTINATION@GMAIL.COM
Hello good afternoon.
Great class. Congratulations!
Just a question: I would like to inform you how to consolidate the data so that there are no duplicates (the repeated ones will be in only one row according to the item).
Example:
Worksheet 1 I have 2 units of Apples and 1 unit of Banana.
Worksheet 2 I have 1 unit of Apple.
That way, when I consolidate in the Master worksheet, a line of Apples with 3 units and a line of Bananas with 1 unit will appear.
Thanks for your appreciation! there is another tutorial which can help you to find appropriate code for removing duplicates...ruclips.net/video/wIVNLRnyEuY/видео.html
Great example and application. I deployed it in a command button in my user-form and its runs perfectly but for some reason while it transfers the data once and in the correct order it also creates another copy of the entries below making the worksheet 600 lines instead of 300. Any thoughts on this ? Keep up the great job !
It is work sooooo good sir.thank you
Most welcome
I am in option explicit mode
I keep getting debug errors to define the variables for "totalsheets" and "i"
so i set both their dimensions to "Dim totalsheet As worksheets, i as worksheet" then i errors out again as a type mismatch
What datatype should be totalsheet and worksheet be?
Please help!!!!
luke shelly remove option explicit mode. Then it ll run
It was nice video . Thank you
☺️
Thanks sir. Very useful.👍👍
Welcome 👍
@@ExcelDestination sir, I am getting complie error : sub or function not define. Pls can you help
Would it be possible to get the code? I believe this is exactly what I need to do but I am not familiar with writing code.
pls. send me email at EXCELDESTINATION@GMAIL.COM I will check in my computer, If I have original file with me then I will provide you that. Thanks for Watching!
Hi, I wrote a VBA that copies from Mastersheet if the value is equals to specific one the copy entire row on specific sheet. My issue, it keeps copying the same data duplicating the same entries, how can I correct that?
You can take help to prevent duplicates, from following tutorial :
ruclips.net/video/yPUCx9HTVvo/видео.html
Thankyou very much 😊
welcome
Thank you, But when i run this macro multiple time it will copy again same data in mastersheet. please help me.
you can check following tutorial for vba code to prevent duplicates.
ruclips.net/video/yPUCx9HTVvo/видео.html
Hi, When i run the script, it keeps prompting Run time error '1004' : Application-defined or object-defined error. Then it highlights this sentence:
Lastrow = Worksheets(i).Cells(Rows.Count, 1).End(x1Up).Row
Please advise. Thanks!
I can not say anything without looking at your code. However, you can understand Run time error '1004' by checking this tutorial, I have made for this specific error
ruclips.net/video/-QpqFg6kOPw/видео.html
@@ExcelDestination Thank you for the video! I've actually emailed you with my codes. Could you please help me take a look? Thanks!
Hi,
My data on each sheet is in column B. But not on 2nd row, it is pasted anywhere in column B.
But the common thing is header has content "Sr No". I want my macro to select that cell containing "Sr No" till lastrow.
Please advice, how can I do it.
Thanks for your comment. For any excel vba support, you can write to exceldestination@gmail.com
How do i change code to select data where the headers are not at top of sheet in each sheet eg labels for data are in B5 >>>Q5
change the code accordingly
Thank you bro..it worked flawlessly ❤️ But I want to ask if I have to consolidate after I have added data in any of the sheets. Then how will it consolidate without repetition?
you can clear mastersheet then it will paste new data every time
nice vedio sir
does this code work for 31 sheet i have to transfer all the data for the whol month to one master sheet
It would work. However, It would take longer time if data is large on all sheets.
Dear Friend, This video is very helpful. And also could you please post a video like this, related to, header column will be same in all worksheets of different workbooks particular sheet and how to Combine the desired data sheets datas from multiple workbooks to a single worksheet.
Ok. Will create soon.
Merge multiple excel files with multiple sheets/every file in a folder, the resulting data file is directly put into a single sheet (the files to be joined have the same structure, and the title starts from A1), how to use VBA?
you are a start ... excellent and simple... for those who need code
Sub Combine()
'
' Combine Macro
'
'
totalsheets = Worksheets.Count
For i = 1 To totalsheets
If Worksheets(i).Name "Conosle" Then
lastrow = Worksheets(i).Cells(Rows.Count, 1).End(xlUp).Row
For j = 2 To lastrow
Worksheets(i).Activate
Worksheets(i).Rows(j).Select
Selection.Copy
Worksheets("Console").Activate
lastrow = Worksheets("Console").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Console").Cells(lastrow + 1, 1).Select
ActiveSheet.Paste
Next
End If
Next
End Sub
Thank you for posting the code Krishan.
If you have one sheet which do not have any data but have only heading then in such case how to do consolidation
There would be some change required in this code. like considering headings only one time from specific sheet and considering data from 2nd row onward on other sheets and if data is not from 2nd row onwords.
Hello. How to transfer userform data to any sheet matching combobox critira to different sheet
Hello Shri...thanks for watching this tutorial...you can take help from my another tutorial, which is for userform...link is ruclips.net/video/q7saNTPzk1g/видео.html
even if it doesn't solve your problem...send me your file with some explanation of your requirement at EXCELDESTINATION@GMAIL.COM
dear sirs, may I have the code
why you don't use
for each ws in thisworkbook.worksheet
Hi, The Video is very helpful. I was wondering how do we extract data from another workbook using the same VBA Macro. As, i have a shared file and macro's cannot be enabled on a shared excel file i have created a similar one to and a added a master sheet to it., now need to select the source from the shared file.
Hi Vignesh, Thanks for watching and sharing your requirement. There is one more tutorial, I have uploaded to combine multiple workbooks. And you are absolutely right that shared files are tough to consolidate through VBA.........
Excel Destination thank you very much, i will
go through them as well. 👍🏽
Great video Sir. Would this work if I only need to gather data from 12 of my 15 worksheets? I was thinking if you could add multiple entries on the main If statement. Thank you for making the video!
Yes, It should work.
@@ExcelDestination Sir, would I separate the additional sheets using "and" or a comma?
IF WorkSheets(i).Name "MasterSheet" and "WCSS" and "name" etc or
IF WorkSheets(i).Name "MasterSheet", "WCSS", "name" etc?
My apologies for asking too much questions. This is my last step to complete a project for work and I am not a "computer person." Thank you for any help / advice you could provide.
@@nsanerydah Did you ever get a response on this?
Zach Elkort not the second question. I believe I could test it to see if it worked though. I have sense moved on to another project with new challenges. Love searching for answers and finding new ways to reach my goal. I guess I should’ve went for a Computer Science degree instead of Criminal Justice...🤔
What if I have to get data from the two sheets only apart from master sheet then? Pls suggest ASAP
you can use same code, but skip master sheet with one if condition like :
if activesheet.name " master sheet" then
-----code---
end if
Actually there are 3 sheets other than master sheet like sheet 1 ,2 n 3 but I want to take data from only 1 n 2 and skip 3
very good sir plz make a video with real job projects base there all condition
sure!
Could you please share me the code for consolidate the data in to a single file
Let me check, if I have the original file
may I hv the code
This is great, but if you have dozens of tabs with hundreds of rows and columns, this will take a very long time. How can you do this by tab instead of by row one at a time, i.e. if you have 100 rows, you just copy all 100 rows at once instead of one at a time?
Sir plz tell me how can create i the command button
pls check out the following tutorial for adding a command button and assigning any code to command button :
ruclips.net/video/U4lf-GN0KSo/видео.html
Can you please reply me with the vba code so I use it?
let me check, if I have the original file still with me.
This code is giving me header again and again. How to remove the same headers and get only one instead along with data
How do I limit to only 4 columns?
some additional code would be required.
Excellent and very easy to understand. Can I have a code, where I have four files with similar data and all those to be combined into a single sheet in the fifth file?. Please
I want learn VBA language please suggest.
if you are interested in online learning, I can teach you...send your email address and mobile at exceldestination@gmail.com
Can you show an example of consolidating same sheets but from multiple spreadsheets?
you mean same sheet from multiple files ?
@@ExcelDestination yep, consolidating from different excel files. In this sample, the tabs are from a single excel file
i have error this code sir please help out
Please send me your code at exceldestination@gmail.com
PLS SHARE PRWCTICE FILE
If I want to avoid two sheets
you can use if condition to avoid two sheets name. for specific code, you can always send me email at exceldestination@gmail.com
Excel Destination I have sent the email to you , pls resolve this
How consolidate only the heading from multiplesheet into one sheet ? Plz help
Thanks kunal for watching! you have to use less code than explained in this tutorial...send me your file at EXCELDESTINATION@GMAIL.COM...I will respond over the weekend...
Sir plz tell me how can i creat this comman button
I dint understand this code sir, I am a beginner to vba code. Plse give some easy tips to learn vba code
Yes actually it is a little advance VBA Code. But, you keep learning continuously. After some time, it would seem easy to you.
@@ExcelDestination okay.
Sir how to practice vba code? like seeing in google den copying it. Will that method workout? How to remember coding lines exactly.
Hi, I went through this tutorial and very useful. However, i have additional queries, and i have sent you an e-mail. Please could you look into it and help me.
ok, let me check
@@ExcelDestination Hi, Did you get a chance to look into my e-mail query?
useful but has some problems. Like entering/adding data even for repetitive/similar customer or product Id which it should not. It should only update existing data for same id and not make fresh entry in new row for same Id. Data validation required If you can help out
there would be some different code required
Firstly I don't even have basic knowledge of Excel or coding so excuse me my doubt is silly
So, I am getting "error 1004, Select method of Range Class failed"
Now, where did I go wrong?
This video just shows the syntax, but not explained the use of few of the lines which are important and basic to explain.
IT DOES NOT WORK
Code is good but u couldn't able to describe it well
SUBCRIPT OUT OF RANGE!!!