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.
Buy Me a Coffee? Your support is much appreciated!
PayPal Me: https://www.paypal.me/jiejenn/5
Venmo: @Jie-Jenn
Source Code:
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
What is the source code behind the Option Explicit?
Thank you so much! This really works!
Can you please also share how to add an empty row after each pasting of a csv file
Also what if I wanna add filter and remove value of 0.0000 on column E
Ty!
Thank you Jie for publishing this code it worked perfect!
This was my first macro after watching a couple of tutorials… it was easier than expected and will save me a lot of time.
Very much appreciated 🙂
Cheers Duane.
I managed to get this working on Friday. Now it stops at:
Set data_sheet = ThisWorkbook.Worksheets(“Master”)
With Run-time error ‘9’: subscript out of range
Am I running t from the wrong location?
Think I’ve solved it. The Master sheet needs to be .xlsm