Export all sheets to CSV?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tom C. - 18 Jul 2006 21:26 GMT

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

Reply to this Message

Bernie Deitrick - 19 Jul 2006 15:27 GMT

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

> 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

Reply to this Message

Tom C. - 21 Jul 2006 21:17 GMT

Many thanks, Bernie!

> Tom,
>
[quoted text clipped - 28 lines]
>> at a time. Is there a way to export to CSV ALL sheets at once? Thanks. --
>> tom c

Reply to this Message

Rate this thread: