Wise Owl Answers - How Do I Copy Sheets from Multiple Excel Files into One Workbook in VBA?

This video explains how to use VBA to loop through a collection of Excel files in a folder, opening each one, looping through the collection of worksheets and copying each one into a new workbook. You'll learn how to use file system objects in the Microsoft Scripting Runtime library to work with the file and folder structure of your computer.

00:00 The Question
01:11 Looping Through Files in a Folder
04:39 Opening Workbooks and Looping Through Worksheets
06:46 Copying Worksheets into Another Workbook
08:33 Deleting a Worksheet and Saving a Workbook
10:21 Making the Filename Unique
11:55 Checking for Excel Files

[krn]

Thanks Andrew. Great tips on looping through multiple workbooks to combine sheets.

[SGH-rtci]

Magic ! Thank you for sharing your knowledge here. Very helpful code. Thank you !

[enrikekasijas]

I really appreciate your tutorials. They are great;)

[janezklun]

Great tutorial, nicely explained, thank you for sharing, Cheers

[wayneedmondson]

Hi Andrew. Awesome lesson! Packed full of good tips, tricks and techniques.. clear and easy to follow. Thanks for sharing :)) Love your videos.. especially VBA :)) Thumbs up!!

[NaimaEchalhi]

Hi Andrew, I really love your videos, I started discovering the universe of VBA recently and it was a blast finding your well structured and enlightening tutorials.
I have a question please, I would like to copy data of the same format, from various workbooks that are dispatched in 2 folders into a new workbook (to be able to reconciliate these data and generate statistics afterwards) and I want to do so based on two criteria (i.e. copy only the rows that contain in their 3rd cell "pending information" OR "pending in the system"). I do this exercise manually weekly and it is repetitive and time consuming and I would really appreciate if you could help me with a code to do so. THANK YOU SO VERY MUCH !

[PankajNegi]

Sir Great !!!! i really enjoy but i have one question if i have multiple file with same data now need to in one excel tab using VBA.

[sidbell]

Thanks for this - just one question.
In order to be able to access the private Macro Workbook subroutines we go through the same add reference thing at the start, but after doing it there is a "references" folder added to the hierarchy. I notice that when you added the Scripting Runtime library to the project that didn't happen.
Is there a reason why that is? It's quite handy as I can see at a glance whether I've imported all my macros into a new workbook or not
Cheers!

[deninsrmic]

Great tutorial Andrew, brilliantly explained. Is there a tutorial video of Wise Owl where VBA makes it possible to append (combine) all workbooks in a folder into one master workbook. I have got more than 600 workbooks I need to append one on top of each other (all column headers are same) but do want to do that with VBA rather than power query. I think it could be done with Dir function but also FSO. Would it be possible to shoot that kind of video. Many thanks. Denin

[ChetstarSimonA]

Hi Andrew, I am very keen on VBA and have watched many / most of your VBA tutorials - they are so good!! I use vba occasionally in my job, but not really enough to keep improving... I've been learning on and off for a few years and recently built a sudoku solver, just for fun and in an attempt to improve my skills, which kind of worked. However, when I googled other solutions, they were so much more eloquent and efficient. How long does it take to get proficient and solving real world problems like in this tutorial?

[KhalilYasser]

Thank you very much. Can you share the files, please?

[vijayalakshmibadigeru]

Hi, can we create a code generator within VBE by inserting customised tool bar.