2

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.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Alnedru
  • 2,573
  • 9
  • 50
  • 88
  • 1
    Check out the answer you posted, you are just `SavingAs` your current workbook, what you need to do is copy each worksheet into a temporal (new) workbook and save that workbook as .csv – Damian May 13 '19 at 07:10

1 Answers1

0

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
Yane
  • 807
  • 8
  • 16