Save each sheet in a workbook to separate CSV files

excel vba csv
Alex Duggleby picture Alex Duggleby · Sep 12, 2008 · Viewed 138.3k times · Source

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

Graham picture Graham · Sep 12, 2008

@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.

Related questions

How to Insert Double or Single Quotes

I have a long list of names that I need to have quotes around (it can be double or single quotes) and I have about 8,000 of them. I have them in Excel without any quotes and I can copy all …

Read More
Excel: macro to export worksheet as CSV file without leaving my current Excel sheet

There are a lot of questions here to create a macro to save a worksheet as a CSV file. All the answers use the SaveAs, like this one from SuperUser. They basically say to create a VBA function like this: …

Read More
Getting "method saveas of object _workbook failed" error while trying to save an XLSM as CSV

I'm trying to save a macro-enabled Excel workbook as a csv file, overwriting the old one (below I had to change the name of the folder and the Sheet, but that doesn't seem to be the issue). Sub SaveWorksheetsAsCsv() Dim …

Read More