Объединение больших файлов Excel, не могу обойти переполнение буфера

вполне в соответствии с хорошей практикой кодирования, которую Werff уже объяснил вам, вы можете попробовать этот другой (прокомментированный) код:

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

поскольку он использует массивы для хранения данных, он страдает от ограничения максимального размера массива до 65536 строк. Если ваши рабочие книги «страны» «Основные» листы имеют больше строк, чем такое ограничение, тогда необходимо использовать разные методы вставки значений (например, между значениями диапазона)

Здесь есть пара вещей, которые могут сбить ваш макрос.

Во-первых, вы объявляете i, j и k как "целочисленный" тип данных; проблема в том, что максимально возможное значение, которое может содержать целое число, равно 32 767. Если вы пойдете выше этого, вы получите переполнение буфера.

Во-вторых, вы объявляете переменные, которые не используете, и не объявляете переменные, которые используете. Насколько я вижу, j нигде в коде не используется, а finalrow и finalrow2 используются, но нигде не объявлены.

Я бы посоветовал вам заменить ваши integer типы данных, которые могут быть очень высокими, на long типы данных (длинное целое число). Вы также можете использовать более описательные имена для ваших целых чисел, чтобы ваш код было легче читать:

Dim iCycler as integer
Dim kStart as long
Dim FinalRow as Long
Dim FinalRow2 as Long

Пройдите через это и посмотрите, что вы получите.

ОТРЕДАКТИРОВАНО ДОБАВИТЬ:

Вот полный процесс, который должен подойти вам:

Sub Duplicator()

    'Define the source file, sheet, and range
    Dim wbkSource As Workbook
    Dim shtSource As Worksheet
    Dim rngSource As Range
    'Define the target file, sheet and range
    Dim wbkTarget As Workbook
    Dim shtTarget As Worksheet
    Dim rngTarget As Range
    'Define the sheet with the list of countries
    Dim shtControl As Worksheet
    'Prepare control integers
    Dim iLoop As Integer
    Dim lLastRow As Long

    'Define the target file as the active workbook
    Set wbkTarget = ActiveWorkbook
    Set shtTarget = wbkSource.Sheets("Output")
    Set rngTarget = shtTarget.Range("A2")
    Set shtControl = wbkTarget.Sheets("Names")

    'Loop through the list
    For iLoop = 1 To 50
        'Open the source file and assign it to a variable.
        Set wbkSource = Workbooks.Open("C:\path\" & shtControl.Cells(iLoop, 1).Value)
        'Assign the source sheet
        Set shtSource = wbkSource.Sheets("Main")
        'Find the last row of data
        lLastRow = shtSource.Range("A" & Rows.Count).End(xlUp).Row
        'Use the last row to build a source range variable
        Set rngSource = shtSource.Range("A1", "BL" & lLastRow)
        'Check that there is space for the copy. If there is not, create a new sheet for the new data
        If rngTarget.Row + lLastRow > shtTarget.Rows.Count Then
            Set shtTarget = wbkTarget.Sheets.Add
            shtTarget.Name = "Output 2"
            Set rngTarget = shtTarget.Range("A2")
        End If
        'Use the size of rngSource to define the size of the target range
        Set rngTarget = rngTarget.Resize(rngSource.Rows.Count, rngSource.Columns.Count)
        'Duplicate the values over
        rngTarget.Value = rngSource.Value
        'Prepare the target range for the next loop
        Set rngTarget = shtTarget.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        'Close the source file
        wbkSource.Close False
    Next iLoop
End Sub

Благодаря объявлению и использованию переменных код должен быть проще как для написания, так и для чтения. Он также должен работать быстрее, так как использует структуру range1.value = range2.value, которая обходит довольно медленный буфер обмена. Он также содержит проверку, чтобы убедиться, что вы не превышаете 1 048 576 строк данных, что может привести к сбою.