How to Convert All Excel Sheets to CSV With Macros
Instructions
1Start Microsoft Excel and open your workbook.
2
Press "ALT+F8" to open the Macros window. Type "ExportSheetsToCSV" in the Macro name box and then click on "Create."
3
Enter the following code in the VBA editor. This macro will create the CSV files in the same folder as your workbook using sheet names for file names.
Sub ExportSheetsToCSV()
Dim wSheet As Worksheet
Dim csvFile As String
For Each wSheet In Worksheets
On Error Resume Next
wSheet.Copy
csvFile = CurDir & "\" & wSheet.Name & ".csv"
ActiveWorkbook.SaveAs Filename:=csvFile, _
FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Saved = True
ActiveWorkbook.Close
Next wSheet
End Sub
4
Close the VBA edit to go back to your workbook.
5
Press "ALT+F8" again to open the Macros window. Select "ExportSheetsToCSV" from the list and then click on "Run." Wait for Excel to finish converting the sheets.
Source...