How do I save multiple sheets as .csv??

  • Thread starter Thread starter Christine
  • Start date Start date
C

Christine

I'm trying to save an entire file as .csv, but can only
save one sheet at a time (there are 10 total worksheets).

Is there an easy way to do this? Saving each sheet
separately and then compiling them into one file is time
consuming...

Is there VB code that I can use to automate the process?
Anything else? Please help!

Thanks, Christine
 
Christine,

This should work for you

Public Sub CSV2()
Application.DisplayAlerts = False
Sheets.Add
ActiveSheet.Name = "Temp_10001"
For Each ws In Worksheets
If ws.Name <> "Temp_10001" Then
ws.UsedRange.Copy
Sheets("Temp_10001").Range("A1").Activate
x = ActiveSheet.UsedRange.Rows.Count
If x > 1 Then ActiveCell.Offset(x, 0).Select
ActiveCell.PasteSpecial (xlPasteValues)
End If
Next
SaveName = Application.GetSaveAsFilename(, _
"CSV (Comma delimited)(*.csv), *.csv")
ActiveWorkbook.SaveAs Filename:=SaveName, _
FileFormat:=xlCSV
ActiveSheet.Delete
Application.DisplayAlerts = True
End Sub

watch out for the text wrap in the post

Dan E
 
Thank you! This works really well.

Christine
-----Original Message-----
Christine,

This should work for you

Public Sub CSV2()
Application.DisplayAlerts = False
Sheets.Add
ActiveSheet.Name = "Temp_10001"
For Each ws In Worksheets
If ws.Name <> "Temp_10001" Then
ws.UsedRange.Copy
Sheets("Temp_10001").Range("A1").Activate
x = ActiveSheet.UsedRange.Rows.Count
If x > 1 Then ActiveCell.Offset(x, 0).Select
ActiveCell.PasteSpecial (xlPasteValues)
End If
Next
SaveName = Application.GetSaveAsFilename(, _
"CSV (Comma delimited)(*.csv), *.csv")
ActiveWorkbook.SaveAs Filename:=SaveName, _
FileFormat:=xlCSV
ActiveSheet.Delete
Application.DisplayAlerts = True
End Sub

watch out for the text wrap in the post

Dan E




.
Click to expand...
 
Back
Top