In this guide, we’re going to show you how to save each sheet as CSV in Excel.
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
ws.Copy
' 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
