Merge Multiple CSV Files.vb

Option Explicit

Dim wsMerge As Worksheet
Dim RowInsert As Long

Sub Merge_Files()
Const FolderPath As String = ""

Dim Files As String
Dim wbTemp As Workbook
Dim LastRow As Long

Set wsMerge = ThisWorkbook.Worksheets("Merge")

Call ClearMergeWorksheet

RowInsert = 2

Files = Dir(FolderPath + "*.csv")

Do Until Files = ""
        
        Set wbTemp = Workbooks.Open(Files)
        
        With wbTemp.Worksheets(1)
        LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        
                .Range("A2:N" & LastRow).Copy
                wsMerge.Range("A" & RowInsert).PasteSpecial xlPasteValues
                
                Application.DisplayAlerts = False
                wbTemp.Close False
                Application.DisplayAlerts = True
                
                RowInsert = RowInsert + LastRow - 1
        End With
        
        Files = Dir()
Loop

MsgBox "File Merge Complete", vbInformation

End Sub

Private Sub ClearMergeWorksheet()
Dim LastRow As Long

With wsMerge
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row

        If 2 > LastRow Then Exit Sub

        .Range("A2:N" & LastRow).ClearContents

End With

End Sub