One way, but not so fast.
If your workbook's name is Book1.xls, this macro would create a file
named Book1.csv in the same folder with Book1.xls

Sub Worbook2Csv()
Dim Csvname As String, Pdir As String
Dim TmpWB As Workbook, AcWB As Workbook
Dim Fname() As String
Dim SelSh As Sheets, Wsh As Worksheet
Dim filenum
Dim i As Long

Pdir = ActiveWorkbook.path
Csvname = ActiveWorkbook.Name
Csvname = Left(Csvname, InStr(Csvname, ".") - 1)
ChDir Pdir
Set AcWB = ActiveWorkbook
Set SelSh = AcWB.Worksheets

SelSh.Copy
Set TmpWB = ActiveWorkbook

Application.DisplayAlerts = False
Application.ScreenUpdating = False

ReDim Fname(TmpWB.Worksheets.Count - 1)

For Each Wsh In TmpWB.Worksheets
Wsh.Select
TmpWB.SaveAs Filename:=Csvname & "Tmp" & CStr(i), _
FileFormat:=xlCSV, CreateBackup:=False
Fname(i) = TmpWB.FullName
i = i + 1
Next
TmpWB.Close

Open Fname(0) For Append As #1
For i = 1 To UBound(Fname)
filenum = FreeFile
Open Fname(i) For Input As #filenum
Do While Not EOF(filenum)
Line Input #filenum, tmp
Print #1, tmp
Loop
Close #filenum
Kill Fname(i)
Next
Close #1

Name Fname(0) As Replace(Fname(0), "Tmp0", "")

Keiji

cyew wrote:
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