VBA Macro to Combine All Workbooks in a Folder into a Single Master Workbook

Показать описание

This video details the VBA code needed to combine/transfer/consolidate all workbooks within a specified folder into a single master workbook.

The VBA code loops through each Workbook in the specified folder:

1) Opens the workbook
2) Copies/ transfers the first worksheet to the master workbook
3) Closes the workbook
4) Names the new sheet in the master workbook - borrowing the name from the source filename

Once this has been achieved the worksheets in the master workbook are sorted by date (month) order.

VBA functions included in the code are:
1) DIR to retrieve the filename of the files within the specified folder
2)MONTHNAME is used to sort the worksheet names in the master workbook.
------------------------

Рекомендации по теме

Комментарии

What can I say ? Crystal clear explanation of a very useful code. Your comments while writing the code helps me understand how VBA works. I find it very helpful. Thank you

SGH-rtci

Brilliant code and explaination, thank you so mych Chester. I checked several videos on Youtube and your code is the only that works so far.

ouyang_es

Thanks, this is great. I have been looking everywhere for something like this. I love that you explain what the code does, it helps to modify it to what you need it for. Thanks again.

yolanderoux

Thank you very much for this code in my work I need combine 214 file....This served me well 🏆🏆

rtrbs

This was really helpful and straight to the point, thank you.

simplykatm

Clear, concise, and informative as hell. If I could give this more than one like I would. Do you know of any way to open files that are not workbooks? Like PDFs or other file types?

mox

Hi, this really helps me a lot. But how to change the code to sorting according to the initial letter of the file.

liranadayag

Thanks Chester. That's really very informative, very practical, and very excellent.

Alhusam

@ChesterTugwell - this is great, but how would I make this code dynamic where the user would select the folder where their Excel files have been saved? Also, how would I adjust the sort to sort by worksheet name?

LaurieBerkenstock

Joining multiple excel files with multiple sheets/every file, 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?
Hope you make video tutorial about this problem

anhnhatnguyet

Great video! Can I ask, what is the benefit of merging files this way, and not through power query combine and transform function?

MrHenri

Really helpful sir... Thank you very much.

fishfoot

Thanks for helping. can i take partial workbook name for worksheet. For example i have 100 workbooks with the name of different supplier like. Nestle ltd, coco cola beverage ltd, PepsiCo industry. I want to copy 2nd worksheet from workbook and I need partial name for worksheet like nestle, coca cola, PepsiCo. Please help.

k.k

Hey can you give me the code to combine all sheets in a workbook into a single sheet in a new workbook

aakashmb

Hi Chester, question, what if I have a long file name (Super Chester Tugwell File 01 T-1234 00) and I want to remove the first 3 words to be the new file name? Hope you can help

mryagit

1. What does "after:" do?
2. Dir will list the files into an array?

tohneyteoh

Thanks for video . Code working fine but my question is that if files in the folder are of different names then what amendment will be in code. Please reply ...

shakilahmed-gmjn

Removed your counter variable as I don't need it, but it's applying ..xls to the end of the file name in the tab. How can I keep that from appearing?

eriqutube

hi chester i followed your tutorial and i may encounter some error mostly its subscript out of range, although i have specified the correct path and everything

ahmadal-afiq

Hi Chester. Very well explained. Is it possible to have the entire workbook as a tab along the bottom of the workbook. i have 7 sheets per workbook and 11 workbooks that i wish to put in a master file

andrewmatthews

VBA Macro to Combine All Workbooks in a Folder into a Single Master Workbook

VBA Macro to Combine All Workbooks in a Folder into a Single Master Workbook

VBA Excel Combining Multiple Macros (2 or more Macros)

Combine All Open Workbooks Excel VBA Macro

How to Combine Multiple Excel Files using VBA

Combine All Workbooks In Folder Excel VBA Macro

Excel VBA Macro to Combine Worksheets in Excel

Excel VBA Combine Multiple Workbooks Into One

Powerful trick to combine all Excel files in a folder AUTOMATICALLY

Easiest way to COMBINE Multiple Excel Files into ONE (Append data from Folder)

Excel VBA to Combine Multiple Workbooks - Transfer Data from Multiple Excel Files

How To Join 2 Macros Together With And Without VBA Code

How to Merge Excel Files (Without Using VBA) - 4 Easy Ways

Combining Multiple Workbooks Into One (Simple VBA)

Merge Multiple Excel Files into 1 File in just few Seconds !!

Excel macro to combine CSV files (Using Excel VBA)

Run Other Macros in VBA Tip - Excel VBA Quickie 10

Excel VBA: Copy Data from one Sheet to Another (Absolute Vs. Relative Macro Recording)

Combine Data from Multiple Sheets in a Single Sheet

AWESOME Excel trick to combine data from multiple sheets

VBA Macro to Consolidate Multiple Worksheets Data Into Master Worksheet

VBA Macro to Transfer Data from Multiple Workbooks into Master Workbook Automatically

VBA code to consolidate data from multiple sheets to one Excel sheet (Only 8 Line code)

Merge Multiple Excel files into one Excel File with VBA Code

VBA to Consolidate data from Multiple Sheets - Excel VBA Example by Exceldestination