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

[Ay Sticky]

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.

 

[Logit]

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]

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

 

[Logit]

Change "Sheet1" to Gbonagun

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

 

[Ay Sticky]

I

Change "Sheet1" to Gbonagun

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]

When Excel displays and error, it will highlight the line of code in yellow.

Which line is highlighted in yellow ?

 

[Marc L]

A VBA demonstration « to separate these worksheets into individual workbooks text files in csv format saved in same directory » for starters :​

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

 

[Ay Sticky]

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.

 

[Logit]

Again ... which line or lines are highlighted in yellow with the error ?

 

[Marc L]

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 ?​