Saving Multiple Tabs as CSV

  • Thread starter Thread starter snax500
  • Start date Start date
S

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
 
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
 
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
 
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
 
Back
Top