Public Sub ExportAsCSV()
'
' ExportAsCSV Macro
'
'
Dim Sh As worksheet
Dim sPath as String
Dim fName as String
sPath = "C:\Documents and Settings\trickster\My Documents\Voltec\"

For Each Sh In workSheets
sh.copy ' creates new workbook
fname = sh.name
ActiveWorkbook.SaveAs Filename:= _
sPath & fname _
& ".csv", FileFormat:=xlCSV, CreateBackup:=False
Activeworkbook.Close SaveChanges:=False

Next Sh
MsgBox "All Sheets Saved."

End Sub

You need to copy each sheet to a new workbook, which this does.

--
Regards,
Tom Ogilvy


Trickster wrote in message
...
Hi. I've been trying to automate exporting each worksheet in a workbook as
a
separate CSV file. I've concocted my own macro by using the recorder and
adapting several other macros I came across, but it doesn't work (I have
to
admit I'm not really familiar with VBA):

Public Sub ExportAsCSV()
'
' ExportAsCSV Macro
'
'
Dim Sh As Sheet

ChDir "C:\Documents and Settings\trickster\My Documents\Voltec\"

For Each Sh In Sheets
ActiveSheet
ActiveSheet.SaveAs Filename:="C:\Documents and Settings\trickster\My
Documents\Voltec\", FileFormat:=xlCSV, CreateBackup:=False & Fname

Sh.Save
Next Sh
Application.StatusBar = "All Sheets Saved."

End Sub

When I run this, I get Compile error:

User-defined type not defined.

Perhaps someone could help me get it working?