I have a question, I would like to save all my worksheets to seperate csv files and after it is finished the original excel file should be used. I've found some questions related to this on stackoverflow BUT I cannot combine them to make it work :(

Here is what I have at the moment which works:

Sub SaveSheetsAsCsv()

Dim ws As Worksheet

Application.DisplayAlerts = False

For Each ws In ActiveWorkbook.Worksheets
    ws.SaveAs ActiveWorkbook.Path & "\" & ws.Name & ".csv", xlCSV, Local:=True
Next

Application.DisplayAlerts = True


End Sub

The result, I have all my worksheets saved to the same folder BUT then my workbook is named as my last worksheet and if I want to close it says I have to save it ... but instead I would like to have my original excel file active.

Any idea how can I do that?

I've tried to implement this: Keep the same excel but I always get an error :(

Any advice and help would be appreciated.

1 Answers

0
Yane On

The adjustment below copies the sheet to a new book, saves it as CSV and closes.

Sub SaveSheetsAsCsv()

Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook
Application.DisplayAlerts = False

For Each ws In ActiveWorkbook.Worksheets
    ws.Copy
    ActiveWorkbook.SaveAs wb.Path & "\" & ws.Name & ".csv", xlCSV, Local:=True
    ActiveWorkbook.Close
Next

Application.DisplayAlerts = True

End Sub