In this Excel automation tutorial, I will show you how we can write a macro (using Excel VBA) to combine multiple CSV files.
Imaging your boss asks you to combine 10 or 50 CSV files by the end of the day, and you only have couple hours left. If you are going to do this task manually, it will takes a few hours. However, if you can automate this task, then everything can be done in let’s say 5 minutes.
Check out Kite (free AI Coding Assistant) → Link
Buy Me a Coffee? Your support is much appreciated!
PayPal Me: https://www.paypal.me/jiejenn/5
Venmo: @Jie-Jenn
Source Code:
Check out Kite (free AI Coding Assistant) → Kite URL
Sub Merge_CSV_Files() Dim target_workbook As Workbook Dim data_sheet As Worksheet Dim folder_path As String, my_file As String Dim LastRow As Long Set data_sheet = ThisWorkbook.Worksheets("Master") folder_path = "C:\Users\Jie\Google Drive\(YouTube Folder)\_To Upload\Macro to combine CSV Files into One\" my_file = Dir(folder_path & "*.csv") '// Step 1: Clear worksheet If my_file = vbNullString Then MsgBox "CSV files not found.", vbInformation Else: data_sheet.Cells.ClearContents End If '// Step 2: Iterate CSV Files Do While my_file <> vbNullString Set target_workbook = Workbooks.Open(folder_path & my_file) LastRow = data_sheet.Cells(Rows.Count, "A").End(xlUp).Row target_workbook.Worksheets(1).Range("A1").CurrentRegion.Copy data_sheet.Cells(LastRow + 1, "A") target_workbook.Close False Set target_workbook = Nothing my_file = Dir() Loop '// Step 3: Clean up data_sheet.Rows(1).Delete data_sheet.Range("A1").CurrentRegion.RemoveDuplicates 1, xlNo Set data_sheet = Nothing End Sub