Excel VBA to Combine All Workbooks in a Folder Into a Master Workbook
This video demonstrates how to write the VBA code that will combine all workbooks in a specified folder into a master workbook. The video also includes the VBA code to sort the imported sheets by sheet name: in this example by month name. The code is shown below the video.
70+ MUST KNOW EXCEL SHORTCUT KEYS: Download the pdf from our Excel training page
UNDERSTAND & FIX EXCEL ERRORS: Download the pdf from our Excel training page
Learn how to fix these errors: #DIV/0!, #N/A!, #NAME?, #NULL!, #NUM!, #REF! & #VALUE!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
Sub MergeWorkbooks() Dim FolderPath As String Dim File As String Dim i As Long FolderPath = "C:\Users\Blue Pecan\Desktop\Bank Statements\" File = Dir(FolderPath) Do While File <> "" Workbooks.Open FolderPath & File ActiveWorkbook.Worksheets(1).Copy _ after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count) ActiveSheet.Name = Replace(File, ".xlsx", "") Workbooks(File).Close File = Dir() Loop For i = 1 To 12 Worksheets(MonthName(i, True)).Move after:=Worksheets(Worksheets.Count) Next End Sub |
