Merge several worksheets into the same .csv file

Hello,

I have the code which allows me to save a worksheet as a .csv file.

However, the reason I am converting to .cvs is that each worksheet has about 50,000 rows

How can I parse through all the worskheets and save them, one after the other, in the same .csv file?

Here comes the code so far. This subroutine, as far as I understand it (even though I am the author :-) ) will only keep the last worksheet in the .csv file

Public Sub ConvertDataToCSV()

Dim PathToFiles As String
PathToFiles = ThisWorkbook.path & "\" 
Dim DataFile As String
Dim i As Integer, Row As Long, Col As Integer
DataFile = PathToFiles & ThisFileName 'Filename defined as a global variable

If FileFolderExists(DataFile) Then 'subroutine to check whether a file exists
    Dim wb As Workbook
    Application.ScreenUpdating = False ' turn off the screen updating
    'open the source workbook, read only
    Set wb = Workbooks.Open(DataFile, True, False)
    i = 0
    Dim First_Col As Integer, First_Row As Long
    Dim Last_Col As Integer, Last_Row As Long
    Dim sht As Worksheets
    Dim FileName As String
    Dim NumberOfWorksheets As Integer
    NumberOfWorksheets = wb.Worksheets.count
    Dim TemporaryTable() As Variant
    For i = 1 To NumberOfWorksheets
        First_Row = wb.Worksheets(i).Cells.Find(What:="*", SearchDirection:=xlNext, SearchOrder:=xlByRows).Row
        First_Col = wb.Worksheets(i).Cells.Find(What:="*", SearchDirection:=xlNext, SearchOrder:=xlByColumns).Column
        Last_Row = wb.Worksheets(i).Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
        Last_Col = wb.Worksheets(i).Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
        If First_Col > 1 Then
            For Col = 1 To First_Col - 1
                Application.DisplayAlerts = False
                wb.Worksheets(i).Columns(Col).EntireColumn.Delete
            Next Col
            First_Col = 1
        End If
        FileName = "SavedFile.csv"
        FileName = PathToFiles & FileName
        Application.DisplayAlerts = False
        'Forcing US parameters with Local:=False => comma. Do not know how to force semi-colon
        wb.Worksheets(i).SaveAs FileName:=FileName, FileFormat:=xlCSVWindows, CreateBackup:=False, Local:=False
    Next i
    With wb
        Application.DisplayAlerts = False
        .SaveAs FileName:=DataFile, FileFormat:=56
        .Close SaveChanges:=False
    End With

    Set wb = Nothing ' free memory
Else
    MsgBox "No new references available"
End If

Application.ScreenUpdating = True ' turn on the screen updating

End Sub

And by the way, if anyone knows how to save a .csv with semi-colon separator I will be infinitely grateful, too

Thank you in advance

Hi NiceLittleRabbit

you could adapt this

ConvertDataToCSV
    Dim ExpRng As Range
     Dim ws As Worksheet
    Open ThisWorkbook.Path & "\csvfileone.csv" For Output As #1
         For Each ws In Worksheets
    Set ExpRng = ws.Range("A1").CurrentRegion
    FirstCol = ExpRng.Columns(1).Column
    LastCol = FirstCol + ExpRng.Columns.Count - 1
    FirstRow = ExpRng.Rows(1).Row
    LastRow = FirstRow + ExpRng.Rows.Count - 1
        For r = FirstRow To LastRow
            For c = FirstCol To LastCol
                data = ExpRng.Cells(r, c).Value
                If data = "" Then data = ""
                If IsNumeric(data) Then data = Val(data)
                If c <> LastCol Then
                    Write #1, data;
                Else
                    Write #1, data
                End If
            Next c
        Next r
   
   Set ExpRng = Nothing
    Next ws
Close #1
End Sub

hi NiceLittleRabbit,
if you place all the data in one worksheet you could then save that one sheet as a csv file
you cant save more that one sheet as one csv file by saveas