merge_csv_macro

gistfile1.txt

Sub MergeCSVFiles()
    Dim FolderPath As String
    Dim FileName As String
    Dim WS As Worksheet
    Dim WB As Workbook
    Dim LastRow As Long
    Dim DataRange As Range
    Dim TargetRow As Long

    ' Prompt the user to select a folder
    FolderPath = Application.FileDialog(msoFileDialogFolderPicker).Show
    If FolderPath = False Then Exit Sub
    FolderPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"

    ' Add a new worksheet to consolidate data
    Set WS = ThisWorkbook.Sheets.Add
    WS.Name = "MergedCSV"

    ' Initialize the row where data will be added
    TargetRow = 1

    ' Loop through each CSV file in the folder
    FileName = Dir(FolderPath & "*.csv")
    Do While FileName <> ""
        ' Open the CSV file
        Set WB = Workbooks.Open(FolderPath & FileName)

        ' Copy data from the first sheet of the CSV
        Set DataRange = WB.Sheets(1).UsedRange
        LastRow = DataRange.Rows.Count

        ' Paste data into the consolidated worksheet
        DataRange.Copy
        WS.Cells(TargetRow, 1).PasteSpecial Paste:=xlPasteValues
        TargetRow = TargetRow + LastRow

        ' Close the CSV file
        WB.Close SaveChanges:=False

        ' Get the next file
        FileName = Dir
    Loop

    MsgBox "CSV files merged successfully!", vbInformation
End Sub