Cosolidating large excel files, cant go around buffer overflow

Question

I am trying to consolidate multiple large excel files into one single file using the following code

Sub Macro1()
    Application.DisplayAlerts = False
    Dim Country As String
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer

    k = 2
    For i = 1 To 50
        Windows("Try2").Activate
        Country = Worksheets("Names").Cells(i, 1).Value
        Workbooks.Open Filename:= "C:path\" & Country & " "
        ActiveWorkbook.Sheets("Main").Activate
        finalrow = Cells(Rows.Count, 1).End(xlUp).Row

        Workbooks(Country).Sheets("Main").Range(Cells(1, 1), Cells(10000, 64)).Copy
        Workbooks("Try2").Sheets("Output").Activate
        Workbooks("Try2").Sheets("Output").Cells(k, 2).PasteSpecial xlPasteValues
        Range(Cells(k, 1), Cells(k + 10000, 1)) = Country
        finalrow2 = 10002 + k
        k = finalrow2 + 1
        Workbooks(Country).Sheets("Main").Activate
        Workbooks(Country).Close SaveChanges:=False
    Next i
End Sub

However after consolidating 2-3 files excel it is throwing buffer overflow error. We have around 50 files. I am trying to figure out if this is just a problem with excel unable to handle large files or there is something wrong with my code. Is there any way to guide excel to handle larger files sizes?

Answer 1

quite along the lines of good coding practice Werff already explained to you, you could try this other (commented) code:

Sub Macro1()
    Dim outputSht As Worksheet '<--| declare a variable to set your "output" sheet to
    Dim countryData As Variant, countryNames As Variant '<--| declare arrays to store "country names" and "country data" in
    Dim country As Variant '<-- "countries" looping variable

    Application.Calculation = xlCalculationManual '<-- disable calculations
    Application.ScreenUpdating = False '<-- disable screen updating


    With Workbooks("Try").Worksheets("Names") '<--| reference "country names" worksheet
        countryNames = Application.Transpose(.Range("A1", .Cells(.Rows.count, 1).End(xlUp)).Value) '<--| store country names in column "A" from row 1 down to last not empty row
    End With

    Set outputSht = Workbooks("Try").Worksheets("Output") '<--| set "output" worksheet
    For Each country In countryNames '<-- loop through countries stored in 'countryNames'

        With Workbooks.Open(FileName:="C:path\" & Country).Sheets("Main") '<--| open current country workbook and reference its "Main" sheet
            countryData = .Range("BL1", .Cells(.Rows.count, 1).End(xlUp)).Value '<--| store current country data in 'countryData' array
            .Parent.Close SaveChanges:=False '<--| close current country workbook
        End With

        With outputSht '<--| reference output sheet
            With .Cells(.Rows.count, 1).End(xlUp).Offset(1).Resize(UBound(countryData, 1)) '<--|reference its column A range from first empty cell after last not empty cell down to as many rows as current country array has
                .Value = country '<--| write current country name in referenced range
                .Offset(, 1).Resize(, 64).Value = countryData '<--| write country data array content from column B rightwards
            End With
        End With
    Next country

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

since it uses arrays to store data, it suffers from the arrays maximum size limitation to 65536 rows. If your "countries" workbooks "Main" sheets have more rows than such a limit then different pasting values techniques must be adopted (like between range values)