This post will guide you how to save each worksheet to separate CSV files in your workbook in Excel. How do I save multiple or all worksheets to separate csv file in Excel 2013/2016. How to export all worksheets to separate txt files with VBA macro in Excel.

1. Export Each Sheet to Separate CSV Files

Assuming that you have a workbook that has four different worksheets, sheet1, sheet2, sheet3, and sheet4. And You want each worksheet as its own CSV file and we also want the CSV file name to take into account its Excel Source as well as the workbook where it originates. To achieve the result of exporting multiple worksheets separate csv files, you have to use VBA Macro to save all worksheets in the current workbook to separated csv files. Here are the steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

Step4: paste the below VBA code  into the code window. Then clicking “Save” button.

Public Sub SaveWorksheetsAsCsv()
Dim WS As Excel.Worksheet
Dim SaveToDirectory As String
Dim CurrentWorkbook As String
Dim CurrentFormat As Long
 CurrentWorkbook = ThisWorkbook.FullName
 CurrentFormat = ThisWorkbook.FileFormat
' Store current details for the workbook
      SaveToDirectory = "D:\tmp\"
      For Each WS In ThisWorkbook.Worksheets
          WS.SaveAs SaveToDirectory & WS.Name, xlCSV
      Next
 Application.DisplayAlerts = False
  ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
 Application.DisplayAlerts = True
' Temporarily turn alerts off to prevent the user being prompted
'  about overwriting the original file.
End Sub

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

Step6: you would see that all worksheet has been converted to a single CSV files in the specified directory. This example will save csv file into D:\tmp\ directory. You need to change the variable “SaveToDirectory ” as you need. or you can also create a temp directory called tmp in disk D partition.

2. Export Each Sheet to Separate Text Files

If you want only convert each worksheet to separate text files for your active workbook, you can also use an Excel VBA Macro to achieve the result. Just using the following VBA code:

Public Sub SaveWorksheetsAsCsv()
Dim WS As Excel.Worksheet
Dim SaveToDirectory As String
Dim CurrentWorkbook As String
Dim CurrentFormat As Long
 CurrentWorkbook = ThisWorkbook.FullName
 CurrentFormat = ThisWorkbook.FileFormat
' Store current details for the workbook
      SaveToDirectory = "D:\tmp\"
      For Each WS In ThisWorkbook.Worksheets
          WS.SaveAs SaveToDirectory & WS.Name & ".txt"
      Next
 Application.DisplayAlerts = False
  ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=xlText
 Application.DisplayAlerts = True
' Temporarily turn alerts off to prevent the user being prompted
'  about overwriting the original file.
End Sub

Let’s see the result:

3. Conclusion

By using VBA code, you can create a custom macro that will iterate through each sheet in your workbook and save it as a separate CSV file. This code can be customized to suit your specific needs and can be easily modified to include additional functionality or to save the files in a different format.

Overall, if you frequently work with large Excel workbooks that contain multiple sheets, using VBA code to save each sheet as a separate CSV file is a great way to streamline your workflow and improve your productivity.

4. Video: Export Each Sheet to Separate CSV Files

This Excel video tutorial, we’ll explore two methods to save or export each sheet to separate CSV files. We’ll start by using the built-in Save As feature, followed by a VBA script for automation.

https://youtu.be/XKnOhtLZmKU