Exporting Data into a CSV - Excel VBA

You can write to a CSV quite simply using VBA.An example could be:

Sub WriteCSVFile()Dim My_filenumber As IntegerDim logSTR As StringMy_filenumber = FreeFilelogSTR = logSTR & Cells(1, "A").Value & " , "logSTR = logSTR & Cells(2, "A").Value & " , "logSTR = logSTR & Cells(3, "A").Value & " , "logSTR = logSTR & Cells(4, "A").ValueOpen "C:\USERS\Documents\Sample.csv" For Append As #My_filenumber    Print #My_filenumber, logSTRClose #My_filenumberEnd Sub

Use the .move to make a new book of the target sheet, then .saveas the newly created book as a CSV. Adjust the Pathname to adjust the directory where you want your csv saved.

    Pathname = "" & Thisworkbook.path & "YourName.csv"    Sheets("Sheet you want as CSV").Move    ActiveWorkbook.SaveAs Filename:=PathName, _        FileFormat:=xlCSV, CreateBackup:=False

Just modified the code by @CharlieSmith to a fairly simple and more usable code, which will convert all the sheets in your workbook to new csv files named with respective sheet names.

Sub WriteCSVFile()Dim i As IntegerDim WS_Count As IntegerWS_Count = ActiveWorkbook.Worksheets.CountFor i = 1 To WS_CountDim ws As WorksheetSet ws = ThisWorkbook.Worksheets(i)     PathName = "" & ThisWorkbook.Path & "\" & ws.Name & ".csv"    ws.Copy    ActiveWorkbook.SaveAs Filename:=PathName, _        FileFormat:=xlCSV, CreateBackup:=FalseNext iEnd Sub

Hope this helps