Welcome to the forum!
Try changing Main() to suit and see if this helps.
Sub Main() Dim ws As Worksheet, i As Integer, p$
p = ThisWorkbook.Path & "\"
For i = 3 To 4
Set ws = Worksheets(i)
RangeToCSVfile ws.UsedRange, p & ws.Name & ".csv"
Next i
End Sub
Sub RangeToCSVfile(Optional aRange As Range, Optional csvFile As Variant = "", _
Optional Overwrite As Boolean = True)
Dim calc As Integer, tf As Boolean, ws As Worksheet
'Tools > Settings > Microsoft Scripting Runtime
'Dim f As Folder, fso As New FileSystemObject
Dim f As Object, fso As Object
With Application
.EnableEvents = False
calc = .Calculation
.Calculation = xlCalculationManual
End With
'Set aRange if needed.
If Selection.Count > 1 And aRange Is Nothing Then Set aRange = Selection
On Error GoTo TheEnd
If aRange Is Nothing Then _
Set aRange = Application.InputBox("Range", "Selected: ", _
"=" & Selection.Address(external:=True), Type:=8)
On Error GoTo 0
'Check if folder in passed csvFile exists.
Set fso = CreateObject("Scripting.FileSystemObject")
If csvFile <> "" Then
tf = fso.FolderExists(fso.GetParentFolderName(csvFile))
End If
If tf = False Or csvFile = "" Then
csvFile = ThisWorkbook.Path
csvFile = Application.GetSaveAsFilename(csvFile, "Comma Separated Text (*.CSV), *.CSV")
End If
If csvFile <> "" And csvFile <> False Then
'Add new workbook, copy/paste aRange, name sheet 1.
Set ws = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
aRange.Copy ws.[A1]
'ws.Name = fso.GetBaseName(csvFile)
If Overwrite Then Application.DisplayAlerts = False
ws.Parent.SaveAs csvFile, xlCSV, CreateBackup:=False
Application.DisplayAlerts = True
ws.Parent.Close False
End If
TheEnd:
Set fso = Nothing
With Application
.CutCopyMode = False
.EnableEvents = False
.Calculation = calc
End With
End Sub