Saving Multiple Tabs as CSV

[snax500]

In Excel2003, I have many sheets that I want to save as individual CSV
files. For example, I have forty sheets in one file that I will end up
with 40 CSV files. I want the files to called by their tab name and
saved @ c:\temp. Any help would on a macro would be appreciated.
Thanks

 

[Ron de Bruin]

Hi snax500

See
http://www.rondebruin.nl/copy6.htm

Read the information below the macro about changing the code for csv

 

[fisch4bill]

Try this (substitute the file name you're working on for YourFileName and the
path you wish to save your csv files to for YourPathName in the code - be
sure to use the quotes as indicated):

Sub SaveSheetsAsFiles()
Dim TabName As String
Dim Sheet As Worksheet
Sheets(1).Activate
On Error Resume Next
For Each Sheet In Sheets
Windows("YourFileName").Activate
TabName = ActiveSheet.Name
MsgBox TabName
ActiveSheet.Next.Activate
Cells.Copy
Workbooks.Add
ActiveSheet.Paste
ActiveWorkbook.SaveAs Filename:="YourPathName" & TabName & ".csv",
FileFormat:=xlCSV
Next Sheet
End Sub

 

[fisch4bill]

Forgot about the posting's formatting issues. The code:
[ActiveWorkbook.SaveAs Filename:="YourPathName" & TabName & ".csv",
FileFormat:=xlCSV] needs to be all one line or have the line continuation
characters such as:
ActiveWorkbook.SaveAs Filename:="YourPathName" & TabName & ".csv", _
FileFormat:=xlCSV

 

[keiji kounoike]

Try this one.

Sub sheets2csv()
Dim vPath As String
Dim Acbk As Workbook
Dim sh As Worksheet

vPath = "C:\temp"
ChDir vPath
Set Acbk = ActiveWorkbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each sh In Worksheets
sh.Copy
ActiveWorkbook.SaveAs filename:=ActiveSheet.Name, FileFormat:=xlCSV
ActiveWorkbook.Close
Acbk.Activate
Next

End Sub

Keiji