Saving worksheets to separate CSV files

[prvince]

Hi,

I'm new to VBA and I've been trying to record a macro/write VBA to save worksheets (in the same workbook) as individual .csv files. When I originally recorded the macro, it worked there and then but when I tried it again, it came up with run-time error 1004 (see below). I've tried updating the underlying VBA to the macro (see further below) but it's still giving the same error message. I'm at a loss after searching and reading different VBA codes online, mainly because I'm unable to understand some of the more complex VBA code.

Can anyone help, please? Any help and explanation would be greatly appreciated!

----------
<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px 'Helvetica Neue'} p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px 'Helvetica Neue'; min-height: 12.0px} </style>
Run-time error '1004':


The file could not be accessed.Try one of the following:


• Make sure the specified folder exists.
• Make sure the folder that contains the file is not read-only.
• Make sure the file name does not contain any of the following characters: [ ] or :
• Make sure the file/path name doesn't contain more than 218 characters.

----------

<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff} p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff; min-height: 13.0px} p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993; background-color: #ffffff} span.s1 {color: #011993} span.s2 {color: #000000} </style>Sub save_to_csv()
'
' save_to_csv Macro
'


'
Sheets("Apple").Select
Sheets("Apple").Copy
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:micheal_jansen:Downloads:Fruit - Apple.csv", FileFormat _
:=xlCSV, CreateBackup:=False
ActiveWorkbook.Saved = True
ActiveWindow.Close
Sheets("Banana").Select
Sheets("Banana").Copy
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:micheal_jansen:Downloads:Fruit - Banana.csv", FileFormat _
:=xlCSV, CreateBackup:=False
ActiveWorkbook.Saved = True
ActiveWindow.Close
Sheets("Cherry").Select
Sheets("Cherry").Copy
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:micheal_jansen:Downloads:Fruit - Cherry.csv", FileFormat _
:=xlCSV, CreateBackup:=False
ActiveWorkbook.Saved = True
ActiveWindow.Close
Sheets("Date").Select
Sheets("Date").Copy
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:micheal_janseng:Downloads:Fruit - Date.csv", FileFormat _
:=xlCSV, CreateBackup:=False
ActiveWorkbook.Saved = True
ActiveWindow.Close
Sheets("Elderberry").Select
Sheets("Elderberry").Copy
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:micheal_jansen:Downloads:Fruit - Elderberry.csv", FileFormat _
:=xlCSV, CreateBackup:=False
ActiveWorkbook.Saved = True
ActiveWindow.Close
Sheets("Fig").Select
Sheets("Fig").Copy
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:micheal_jansen:Downloads:Fruit - Fig.csv", FileFormat _
:=xlCSV, CreateBackup:=False
ActiveWorkbook.Saved = True
ActiveWindow.Close
Sheets("Grape").Select
Sheets("Grape").Copy
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:micheal_jansen:Downloads:Fruit - Grape.csv", FileFormat _
:=xlCSV, CreateBackup:=False
ActiveWorkbook.Saved = True
ActiveWindow.Close


<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff} p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #008f00; background-color: #ffffff} p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff; min-height: 13.0px} p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993; background-color: #ffffff} span.s1 {color: #011993} span.s2 {color: #000000} </style>
End Sub

 

[sergioMabres]

Hi micheal
I think (not sure) that the problem is when you save your file as CSV you lose all other sheets but the one you just saved, so I would use some thing like this

...   
Sheets("Apple").Copy
ActiveWorkbook.SaveAs Filename:="Macintosh HD:Users:micheal_jansen:Downloads:Fruit - Apple.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
Windows("OriginalWrkbookNameHere").Activate
Sheets("Banana").Select
Sheets("Banana").Copy
'(and so forth)
...

So you copy the sheet to another file before saving as CSV and then get back to the original workbook
Remember to change "OriginalWrkbookNameHere" in the code for your real workbook name
I hope this hepls
Cheers
Sergio