Save each sheet in a workbook to separate CSV files

Question

How do I save each sheet in an Excel workbook to separate CSV files with a macro?

I have an excel with multiple sheets and I was looking for a macro that will save each sheet to a separate CSV (comma separated file). Excel will not allow you to save all sheets to different CSV files.

Answer 1

@AlexDuggleby: you don't need to copy the worksheets, you can save them directly. e.g.:

Public Sub SaveWorksheetsAsCsv() Dim WS As Excel.Worksheet Dim SaveToDirectory As String      SaveToDirectory = "C:\"      For Each WS In ThisWorkbook.Worksheets         WS.SaveAs SaveToDirectory & WS.Name, xlCSV     Next  End Sub 

Only potential problem is that that leaves your workbook saved as the last csv file. If you need to keep the original workbook you will need to SaveAs it.