In this guide, we’re going to show you how to save each sheet as CSV in Excel.

Download Workbook

Excel to CSV

Saving an Excel worksheet as a CSV file is an easy task. All you need to do is to use Save As section in File menu and select the CSV as the file type.

This action allows you to save the active worksheet as a CSV file. The downside of this approach is repetitiveness. You need to save as each worksheet manually.

Although a CSV file cannot preserve colors, formatting options or other stuff, Excel keeps them in the opened workbook as long as it remains open. Thus you can always save as an Excel file after creating CSV files.

The workaround is to use VBA to save each sheet as CSV like any other repetitive job in Excel. You can either record a macro while you are saving a worksheet as CSV and create a loop to repeat for each worksheet or use the following code.

If you are new to VBA macros, you can check How to create a macro in Excel article to start to save each sheet as CSV automatically.

VBA code for saving each sheet as CSV

Sub SaveAsCSV()
    Application.ScreenUpdating = False

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim wbNew As Workbook
    Dim fullPath As String
    Dim counter As Integer

    Set wb = ActiveWorkbook
    ' loop through each worksheet
    For Each ws In wb.Worksheets
        ' run code only for visible sheets
        If ws.Visible = xlSheetVisible Then
            ' copy the worksheet to a new workbook
            ' select the new workbook
            Set wbNew = ActiveWorkbook
            ' generate a full path for the new file including CSV extension
            fullPath = wb.Path & "\" & Left(wb.Name, InStrRev(wb.Name, ".") - 1) & "_" & ws.Name & ".csv"
            ' disable alerts in case of overwrite confirmation
            Application.DisplayAlerts = False
            ' save the new workbook as a CSV
            wbNew.SaveAs Filename:=fullPath, FileFormat:=xlCSV
            ' re-activate alerts
            Application.DisplayAlerts = True
            ' close the new workbook
            wbNew.Close SaveChanges:=False
            ' increase counter for the information message
            counter = counter + 1
        End If
    Next ws

    ' pop an information message
    MsgBox counter & IIf(counter > 1, " worksheets", " worksheets") & " exported.", vbInformation, "Export Worksheets"

    Application.ScreenUpdating = True
End Sub