How to Combine Multiple Excel Files using VBA

Рет қаралды 31,568

Күн бұрын

How to Combine Multiple Excel Files using VBA
In this video I show 3 examples of reading muliple Excel workbooks to one worksheet. The examples are:
1. Copy all the data.
2. Copy specific columns.
3. Return to the total amount for each item of the 3 files.
#excelvba #excelvbacopy #VBAMultipleFiles #ExcelMultipleFiles
SUBSCRIBE TO THE CHANNEL: bit.ly/36hpTCY
DOWNLOAD THE SOURCE CODE FOR THIS VIDEO: bit.ly/3ej9Lcg
RELATED RESOURCES:
(Blog Post)Excel VBA Copy - The Complete Guide to Copying Data: excelmacromastery.com/excel-v...
(Blog Post)Excel VBA Array - The Complete Guide: excelmacromastery.com/excel-v...
(Blog Post)Excel VBA Dictionary - A Complete Guide
excelmacromastery.com/vba-dic...
KZfaq PlayList: Excel VBA Dictionary: • Excel VBA Dictionary
RELATED TRAINING
The Excel VBA Handbook Course(TheExcelVBAHandbook.com)
Webinar Archives - 60+ Hours of VBA training(excelmacromastery.com/excel-v...)
Free Excel VBA Resources
Excel VBA Articles (excelmacromastery.com/vba-art...)
Useful VBA Shortcut Keys
========================
Debugging:
Compile the code: Alt + D + L OR Alt + D + Enter
Run the code from the current sub: F5
Step into the code line by line: F8
Add a breakpoint to pause the code: F9(or click left margin)
Windows:
View the Immediate Window: Ctrl + G
View the Watch Window: Alt + V + H
View the Properties Window: F4
Switch between Excel and the VBA Editor: Alt + F11
View the Project Explorer Window: Ctrl + R
Writing Code:
Search keyword under cursor: Ctrl + F3
Search the word last searched for: F3
Auto complete word: Ctrl + Space
Get the definition of the item under the cursor: Shift + F2
Go to the last cursor position: Ctrl + Shift + F2
Get the current region on a worksheet: Ctrl + Shift + 8(or Ctrl + *)
To move lines of code to the right(Indent): Tab
To move lines of code to the left(Outdent): Shift + Tab
Delete a Line: Ctrl + Y(note: this clears the clipboard)
Table of Contents:
00:00 - Introduction
01:11 - Code - return all the data
05:05 - Using Dir to get the files
06:12 - Reading the workbook data
08:18 - Writing the data to the worksheet
12:07 - IndividualColumns
14:20 - Totalling Values

Пікірлер: 60

@Excelmacromastery Жыл бұрын

Let me know, in the comments below, which method you found most useful. Make sure to download the source code from the description below the video.

@karl-heinzlutzel9585 Жыл бұрын

Paul, as always a great application. You explain this really super. I think it's great that you show the way over the individual functions or subs. Simply how it should be done professionally.

@Excelmacromastery Жыл бұрын

@@karl-heinzlutzel9585 Thanks. Glad you like it.

@yashchawla8360 Жыл бұрын

Any way to write dictionaries with classes in one go?

@miless2111sutube Жыл бұрын

Given you had to select something to make the dictionary work does any user need to do the same to be able to run the code?

@Excelmacromastery Жыл бұрын

@@yashchawla8360 No. The reason you can do with basic data items is because they are stored as an array.

@budiyanto1531 Жыл бұрын

Paul always shows me A lot little things with the New and simpliest way which is the Best thing to write codes. Thank you Paul. The next thing I would like you to teach me how to make an application complete with its relational databases connected each other. And offcourse, like always, the simpliest way and the fastest aplications.

@mtotowamungu8259 Жыл бұрын

I like your new Camtasia video style, you can see that you put a lot of effort into it. I also like that your videos are more relaxed again (easier to understand for non-native English speakers). Thanks a lot Paul

@Excelmacromastery Жыл бұрын

Thanks. Glad you like it.

@alexrosen8762 Жыл бұрын

Super useful tutorial Helps to improve and streamline automation of work processes.

@HachikoLostdog Жыл бұрын

Very useful and precise as always. Paul could you expand this a little bit further say i have a huge range of data set and i want to get the uniquue names and ids per employee and get the total data they have on the table

@joaocustodio2094 Жыл бұрын

Paul, congratulations. Another great video.

@Excelmacromastery Жыл бұрын

Thanks Joao

@grzegorz2852 Жыл бұрын

man, u are a hero! Thank you very much for this super useful tutorial. i learn a lot from you

@Excelmacromastery Жыл бұрын

Thanks

@henrycullen950 Жыл бұрын

This is exactly what I have been searching for :)

@Excelmacromastery Жыл бұрын

Glad you like it.

@bondniko Жыл бұрын

Very useful.

@inigosanchez-ostizchivite5441 Жыл бұрын

I know almost every object you use but the very reason I watch your videos is because I learn a lot on how to create clean and comprehensive code. I appreciate the work you do.

@Excelmacromastery Жыл бұрын

Thanks

@maciejdolinski2603 Жыл бұрын

Hi Paul. It's great video. As every that you create. Thank you. Anyway, why do you use intermediate table to temporarily keep data from source worksheets instead of copying directly from source sheet to destination sheet using e.g. Advanced Filter? Is it faster or is it to make the code ore readable?

@sedataksakal414 Жыл бұрын

Süper çok güzel bir uygulama. Teşekkür ederim.

@dangreen2501 Жыл бұрын

I went from not knowing anything about VBA coding to being able to understand what you are saying in this video the first time through. The W.W.W is amazing and people like you make it easy to learn new and amazing things. I started out just wanting to create a program that would automate some B.I. Reports I used daily and it has turned into a full blown application that does it all! I advise anyone with the desire to learn VBA to learn from folks like Paul. Thanks dude, you’re awesome!

@Excelmacromastery Жыл бұрын

Thanks Dan. I appreciate the feedback.

@SyedMuzammilMahasanShahi Жыл бұрын

Thanks for this video.

@Excelmacromastery Жыл бұрын

You're welcome

@tomharrington1453 Жыл бұрын

Dude, there is a LOT in this video. Excellent job.

@Excelmacromastery Жыл бұрын

Thanks Tom

@hammeedabdo.82 Жыл бұрын

Hi Paul, Thank you for this video. I want to make a dictionary, so that it contains huge data, so that when I add new sentences to this dictionary, this data must be stored in one of the databases such as Mysql. My question: Is it possible to use Excel as a front-end in order to display this data from the Mysql database, and when we add new sentences it will be stored in the Mysql database?

@teddyted04 Жыл бұрын

Very neat. At the moment, I would say all methods described are useful. Can you clarify one? From WriteData, shAll it gives an error on compilation: "Variable not defined". Would you kind enough to explain? How about the Reference > Microsoft Scripting Runtime. Where is that coming from? Thank you kindly.

@rockyrkt6293 Жыл бұрын

I want create a VBA tool for collecting data from different Excel to one like same, but for flexible for all types of data like may be sometimes raw and column are changed

@ziadsemaan5669 Жыл бұрын

Hello. New to the channel here. Quick question: if the data is jagged what should we use instead of .CurrentRegion

@gunasekaran4288 Жыл бұрын

You Re one of the best developers in this filed sir. Also kindly share work book along with code for my testing. Unable to download this code in browser

@Excelmacromastery Жыл бұрын

Make sure you're not using any popup blockers. Code should download fine.

@gunasekaran4288 Жыл бұрын

@@Excelmacromastery ok sir. Thanks do you have any online training available. I would like to learn from you. But cost if affordable for me...plz.let me know your thoughts sir

@gunasekaran4288 Жыл бұрын

Any update

@Tom-kd3xc Жыл бұрын

Hi! Thanks for the video. What about when only the names of the columns are known?

@Tom-kd3xc Жыл бұрын

I mean, what if we don't know the order of the columns, but only know their names?

@pezad4187 Жыл бұрын

Use Worksheet.Match function to determine the index

@maddinenirajeshbabu3143 Жыл бұрын

Thanks very much paul, Actually i am having one query when we are running the macro we can't work another excel work how to over come this issue even macro is running we have to work parallel another work also is there any way to over come this problem? Thanks in advance Paul.

@Excelmacromastery Жыл бұрын

I'm not really clear on your question. Are you referring to multi threading? If so, it's not possible in VBA.

@VipulKarkar Жыл бұрын

As Paul mentioned multi threading is not possible in excel. The very first thing, you should look into, is optimising the macro so it doesn't run for hours. I follow a convention where If any macro is running more than 3 minutes, I try to re-write, refactor and optimise the code in more efficient way. I hope that helps.

@meniporat3527 Жыл бұрын

Hi Paul, Thank you for an excellent explanation. Unfortunately, the source code file cannot be downloaded 😞

@Excelmacromastery Жыл бұрын

What issue are you having with the download? Make sure to turn off any popup blockers.

@thofiktufel Жыл бұрын

I think Power Query would be better to use here. Can you please explain why choose VBA over PQ for this task ?

@Excelmacromastery Жыл бұрын

See the Power Query version of this task here: kzfaq.info/get/bejne/ntlyftx-ma6xqXU.html

@francsreal7437 Жыл бұрын

I have been following your steps..yet it does not locate the xlsm file i am looking in a folder..

@Thegingeranator Жыл бұрын

I'm curious how reliable these vba codes over the course of time, do they break A lot with updates? is it smart to combine excel formulas with VBA?

@Excelmacromastery Жыл бұрын

The code doesn't break with updstes.

@vishalmastud9256 Жыл бұрын

GOOD LUCK

@sangramkeshary5850 Жыл бұрын

Hi I'm getting error in WriteData() on newRow declaration that "Variable not defined" Could anyone please help me on this?

@JD-kp4dp Жыл бұрын

How about the opposite, one master file sending data to hundreds of separate files?

@nkememes1637 Жыл бұрын

Hello sir, How can i Split a ( 1 Million of rows) large file into 10 small files using VBA macro without filterring dara please help me to code for this problem

@anhnhatnguyet4628 Жыл бұрын

The files in the folder have the same structure, file A has 1 sheet, file B has 2 sheets, file C has 3 sheets. Need to create a file D with only 1 sheet with all data of 6 sheets, use VBA, how to do it?

@Ghulammuhammad360 Жыл бұрын

soft source?

@alexeiofp Жыл бұрын

I am light years away from understanding this level of code. What book do you recommend to start with?

@Excelmacromastery Жыл бұрын

Hi Alexis, I would recommend starting with this tutorial (excelmacromastery.com/vba-tutorial-1/)

@hammeedabdo.82 Жыл бұрын

The other question: How can we divide an excel file into several files? We need another video to reverse this process: How can we divide this excel file in this video again into several files?