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

Hi snax500

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

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"snax500" wrote in message ...
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

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


"snax500" wrote:

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
.

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

"fisch4bill" wrote:

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


"snax500" wrote:

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
.

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

snax500 wrote:
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