You can use the following syntax in VBA to save each sheet in a workbook to a CSV file:

Sub SaveCSV()

Dim Ws As Worksheet
Dim SaveDir As String

Dim CurrentWorkbook As String
Dim CurrentFormat As Long

CurrentWorkbook = ThisWorkbook.FullName
CurrentFormat = ThisWorkbook.FileFormat

'specify directory to save CSV files in
SaveDir = "C:UsersbobbiOneDriveDesktop"

'save each sheet to individual CSV file
For Each Ws In Application.ActiveWorkbook.Worksheets
    Ws.SaveAs SaveDir & Ws.Name, xlCSV
Next

Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
Application.DisplayAlerts = True

End Sub

This particular macro will save each sheet in the currently active workbook to a CSV file.

The CSV files will be saved in the path specified in the SaveDir variable.

Note: The line Application.DisplayAlerts=False tells VBA to turn temporarily turn off any display alerts in Excel when saving the files.

The following example shows how to use this syntax in practice.

Example: Save Sheets as CSV Files Using VBA

Suppose we have an Excel workbook with two sheets.

The first sheet is called player_stats and contains statistics about various basketball players:

The second sheet is called team_info and contains information about various basketball teams:

Suppose we would like to save each of these sheets as individual CSV files on the Desktop of our computer.

We can create the following macro to do so:

Sub SaveCSV()

Dim Ws As Worksheet
Dim SaveDir As String

Dim CurrentWorkbook As String
Dim CurrentFormat As Long

CurrentWorkbook = ThisWorkbook.FullName
CurrentFormat = ThisWorkbook.FileFormat

'specify directory to save CSV files in
SaveDir = "C:UsersbobbiOneDriveDesktop"

'save each sheet to individual CSV file
For Each Ws In Application.ActiveWorkbook.Worksheets
    Ws.SaveAs SaveDir & Ws.Name, xlCSV
Next

Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
Application.DisplayAlerts = True

End Sub

Once we run this macro, each sheet will be saved as a CSV file on our Desktop.

If I navigate to the Desktop on my computer I can see each of these individual CSV files with file names that match the sheet names:

If I open the player_stats CSV file using Notepad, I can see that the values from the Excel file have been saved as comma-separated values:

Note that in this example we were able to save two sheets in our workbook as individual CSV files, but this same macro will work with any number of sheets.