Export all sheets to CSV?

[Tom C.]

Excel 2000, workbook with several sheets. Each sheet is set up exactly the
same way so that the cells are constant from sheet to sheet. I want to
export (or, I guess Save As) CSV ALL worksheets at the same time. The only
way I can see that it works is that you have to save each SHEET one at a
time. Is there a way to export to CSV ALL sheets at once? Thanks. -- tom c

 

[Bernie Deitrick]

Tom,

You could combine all the sheets into one before doing the SaveAs: this macro will combine all
sheets, assuming that the data on each sheet starts in cell A1 and is contiguous.


Sub CombineSheets()
Dim mySht As Worksheet
Dim myShtComb As Worksheet

Set myShtComb = Sheets.Add
myShtComb.Name = "Combined"

For Each mySht In ActiveWorkbook.Worksheets
If mySht.Name <> "Combined" Then
mySht.Range("A1").CurrentRegion.Copy _
myShtComb.Range("A65536").End(xlUp)(2)
End If
Next mySht

End Sub


HTH,
Bernie
MS Excel MVP

 

[Tom C.]

Many thanks, Bernie!

Tom,

You could combine all the sheets into one before doing the SaveAs: this
macro will combine all sheets, assuming that the data on each sheet starts
in cell A1 and is contiguous.


Sub CombineSheets()
Dim mySht As Worksheet
Dim myShtComb As Worksheet

Set myShtComb = Sheets.Add
myShtComb.Name = "Combined"

For Each mySht In ActiveWorkbook.Worksheets
If mySht.Name <> "Combined" Then
mySht.Range("A1").CurrentRegion.Copy _
myShtComb.Range("A65536").End(xlUp)(2)
End If
Next mySht

End Sub


HTH,
Bernie
MS Excel MVP