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