Excel VBA – How To Consolidate Data From Different Sheets?
In this tutorial we are going to merge excel files into one results file for uploading to an accounting system.
A reader’s workflow involved grabbing data from a web system that he logged into on a regular basis, then copying out customer data and pasting it into Excel worksheets, then consolidating that information into a single sheet which would be used to upload to an accounting system.
His question was how to do that using Microsoft Excel VBA without using Select?
I have written a code intensive answer here, if there is sufficient interest I may also look into providing a less code intensive answer to this problem, however the method highlighted here is my go to answer to this problem.
Even if you are not interested in this specific problem you will find the coding techniques used here to be very useful for your other projects.
Check out the solution in the video above, also I can email you the link to the spreadsheet used in this tutorial, just fill out the form below.
If you are new to VBA coding, Start with this book.
Disclosure:- If you buy this or any book using the link above (at the moment amazon.com only). Amazon will give me a small commission.
If you have some coding experience, then this or both books may be best.
Disclosure:- If you buy this or any book using the link above (at the moment amazon.com only). Amazon will give me a small commission.
Dear Sean Jhonson
When trying to construct the code, the first error that is seen is:
Sub or Function not set, CleanUpMultiLineInfo
Please, please allow me the code, otherwise it would be frustrating for me.
Thank you
Jaime
Cusco – Perú
HI Jamie
I sent you an email with the code yesterday. can you check your “spam” folder.
also i fixed the problem yesterday. so if you put your details into the yellow box
above the “system” should recognize your email address and send you to the
download straight away.
Please let me know if this works, also let me know if the problem persists.
Best Regards
Sean
Dear Johnson
I just received your email, thank you very much, at this moment I am starting to review the code.
regards
Jaime
Dear Sean Johson
Definitely this tutorial changes my approach about VBA, it is extraordinary the algorithm that you use, it seems great and as they say in the simplest it shows the great thing. Thanks John for your contribution, it will help me to improve what I was learning from the VBA.
Actually, I am committed to developing a code that can follow a process such as making a piece of furniture, but applying Auto Shapes of Excel where each Shape means a milestone in the process and that serves to show if I am delayed with a color Red or if I’m near the deadlines with a yellow and green if all goes well. Please, if you could comment something about it or else you would recommend me some web address that you know about this subject.
Thank you so much for everything, may Diod bless you.
regards
Jaime Pantigoso
Cusco – Perú
Hi Jamie
Thanks for that.
I suggest getting on top of object oriented programming techniques in vba to write an app like you suggest.
Have a look at the youtube video in this post VBA Class Modules
Sean