Macro to save and separate multiple worksheets as individual files in .csv format

Ay Sticky

New Member
Joined
Oct 18, 2021
Messages
37
Office Version
  1. 2016
Platform
  1. Windows
Please I have multiple worksheets in a workbook saved in xlsx. The task I need the vba for, is to separate these worksheets into individual workbooks in csv format with their names corresponding to the names as used in the worksheets. It would also be great if all separated sheets could all be saved in same directory.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,301
VBA Code:
Option Explicit

Sub ExportToCSVs()
 
Dim ws As Worksheet
Dim nm As String
Application.ScreenUpdating = False
For Each ws In Worksheets
ws.Select
nm = ws.Name
 ActiveSheet.SaveAs Filename:="C:\Users\My\Desktop\" & nm & ".csv", _
     FileFormat:=xlCSV, CreateBackup:=False

 
Next ws
Sheets("Sheet1").Activate
ActiveSheet.Range("A1").Select
Application.ScreenUpdating = True
MsgBox "Done"
End Sub
 

Ay Sticky

New Member
Joined
Oct 18, 2021
Messages
37
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
Option Explicit

Sub ExportToCSVs()
 
Dim ws As Worksheet
Dim nm As String
Application.ScreenUpdating = False
For Each ws In Worksheets
ws.Select
nm = ws.Name
 ActiveSheet.SaveAs Filename:="C:\Users\My\Desktop\" & nm & ".csv", _
     FileFormat:=xlCSV, CreateBackup:=False

 
Next ws
Sheets("Sheet1").Activate
ActiveSheet.Range("A1").Select
Application.ScreenUpdating = True
MsgBox "Done"
End Sub
Got an error after running this (screenshot below). The "Gbonagun" is the name of the first worksheet
1634927297092.png
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,301
Change "Sheet1" to Gbonagun

VBA Code:
Next ws
Sheets("Sheet1").Activate       '<---- change Sheet1 to Gbonagun'
ActiveSheet.Range("A1").Select
 

Ay Sticky

New Member
Joined
Oct 18, 2021
Messages
37
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

I
Change "Sheet1" to Gbonagun

VBA Code:
Next ws
Sheets("Sheet1").Activate       '<---- change Sheet1 to Gbonagun'
ActiveSheet.Range("A1").Select
I've changed the sheet1 to Gbonagun as directed but still got same error.
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,301
When Excel displays and error, it will highlight the line of code in yellow.

Which line is highlighted in yellow ?
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,729
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

A VBA demonstration « to separate these worksheets into individual workbooks text files in csv format saved in same directory » for starters :​
VBA Code:
Sub Demo1()
    Dim F%, Ws As Worksheet, Rw As Range
        F = FreeFile
    With ActiveWorkbook
        For Each Ws In .Worksheets
            Open .Path & "\" & Ws.Name & " .csv" For Output As #F
        For Each Rw In Ws.UsedRange.Rows
            Print #F, Join(Application.Index(Rw.Value, 1, 0), ",")
        Next
            Close #F
        Next
    End With
End Sub
 
Solution

Ay Sticky

New Member
Joined
Oct 18, 2021
Messages
37
Office Version
  1. 2016
Platform
  1. Windows
When Excel displays and error, it will highlight the line of code in yellow.

Which line is highlighted in yellow ?
After pressing cancel from the first error in the screenshot I posted earlier, I later got this on the VBA as below. Also, I have to admit that the main file (containing the multiple worksheets) is originally a csv file. I later "Save as" it as a xlsx file because Excel wouldn't allow me to create multiple worksheets in csv format. Now that I've finished my task on them, I need to save them back individually in csv format.

1634997368947.png
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,301
Again ... which line or lines are highlighted in yellow with the error ?
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,729
Office Version
  1. 2010
Platform
  1. Windows
because Excel wouldn't allow me to create multiple worksheets in csv format
As it is easily doable under Excel - maybe without importing any data to any worksheet - like without using Excel !​
The issue here is your underlevel elaboration …​
Did you try at least my post #7 ?​
 

Forum statistics

Threads
1,148,259
Messages
5,745,715
Members
423,969
Latest member
seanguerrero

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top