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