Chen,

See if this does about what you want.

The line
strCSVpath = "C:\Test\myExport.csv"
needs to be edited to the path and file name you want for the csv file (make
sure the path exists)

'---------------------------------------
Sub MultiSheetCSVexport()

Const ForWriting = 2

Dim myRange As Range
Dim strCSVpath As String
Dim FSO, objTxtFile

strCSVpath = "C:\Test\myExport.csv"

Set FSO = CreateObject("Scripting.FileSystemObject")
Set objTxtFile = FSO.OpenTextFile(strCSVpath, ForWriting, True)

For Each wkSheet In Application.Worksheets
Set myRange = wkSheet.UsedRange
With wkSheet
If myRange.Rows.Count 2 Then
For R = 1 To myRange.Rows.Count
If myRange.Columns.Count 1 Then
For C = 1 To myRange.Columns.Count - 1
objTxtFile.Write .Cells(R, C).Text & ","
Next C
objTxtFile.Write .Cells(R, myRange.Columns.Count).Text &
vbCrLf
Else
objTxtFile.Write .Cells(R, 1).Text & vbCrLf
End If
Next R
Else
If myRange.Columns.Count 1 Then
For C = 1 To myRange.Columns.Count - 1
objTxtFile.Write .Cells(1, C).Text & ","
Next C
objTxtFile.Write .Cells(1, myRange.Columns.Count).Text & vbCrLf
Else
objTxtFile.Write .Cells(1, 1).Text & vbCrLf
End If
End If
End With
Next wkSheet

objTxtFile.Close
Set objTxtFile = Nothing
Set FSO = Nothing

End Sub


'---------------------------------------

Steve Yandl



"cyew" wrote in message
...
Hi

I am new to VBA.

I have data on a number of sheets and I would like to write VBA codes
to loop through each sheet and write out the data in one CSV file.

eg

Sheet1
1 one
2 two
3 three

Sheet2
4 four
5 five
6 sixe


I would like my output CSV file to be:
1,one
2,two
3,three
4,four
5,five
6,sive


Thanks
Chen