Option Explicit
Option Private Module
'====================================================================================================
Sub AllSheetsInArray()
Dim sh As Worksheet
Dim arr, aRes(), r&, c&, nR&, cMax&

ReDim aRes(1 To 100000, 1 To 200)           ' объявляем основной массив с количеством строк и столбцов "с запасом"

For Each sh In ActiveWorkbook.Worksheets    ' цикл по всем листам книги
    arr = sh.UsedRange.Value                    ' берём всю рабочую область листа в массив

    If IsArray(arr) Then                                ' если было только одно значение (получен НЕмассив), то переходим к следующему листу
        If UBound(arr, 2) > cMax Then cMax = UBound(arr, 2) ' вычисляем максимальное количество столбцов из всех массивов листов
        
        For r = 1 To UBound(arr, 1)                         ' цикл по СТРОКАМ ОЧЕРЕДНОГО массива с листа
            nR = nR + 1                                         ' увеличиваем счётчик строк НОВОГО/СБОРНОГО массива

            For c = 1 To UBound(arr, 2)                         ' цикл по СТОЛБЦАМ ОЧЕРЕДНОГО массива с листа
                aRes(nR, c) = arr(r, c)                             ' переписываем очередной элемент из очередного массива в сборник
            Next c
        Next r
    End If
Next sh

Worksheets.Add                              ' добавляем новый лист
Cells(1, 1).Resize(nR, cMax).Value = aRes   ' выгружаем на него только значимую часть нового массива (а не весь массив, объявленный с запасом)
End Sub
'====================================================================================================