How to export multiple worksheets to CSV (without saving over the current worksheet)

Question

I'm trying to export a number of worksheets in my workbook to .csv via some code like this:

Sub Export_To_CSV(exportPath As String)    

    Dim filePath As String 

    For Each WS In ThisWorkbook.Worksheets

            filePath = exportPath & "(" & WS.Name & ").dat"
            WS.SaveAs Filename:=filePath, FileFormat:=xlCSV

    Next 
End Sub

The problem is that this saves over the current .xlsm file that I have open.

How can I get it to export the .csv without changing the name of the current file?

I thought SaveCopyAs would do the trick, but it only applies to a workbook and not a worksheet.

Answer 1

Here comes my idea which could help you... Add this part of code instead you current for...next section:

'...your code here
Dim tmpWS As Worksheet
Application.DisplayAlerts = False
For Each WS In ThisWorkbook.Worksheets

        filePath = exportPath & "(" & WS.Name & ").dat"

        WS.Copy
        Set tmpWS = ActiveSheet
        tmpWS.SaveAs Filename:=filePath, FileFormat:=xlCSV
        tmpWS.Parent.Close False
Next
Application.DisplayAlerts = True
'...your code here

Logic of the code? First, it makes a copy of your sheet into temporary workbook, next it saves new sheet as CSV file and finally, it closes temporary workbook. Moreover, I added Application.DisplayAlerts instructions that your code overwrites .csv file without asking if file already exists.