Sub CollectDataFromAllSheets()
    Dim ws As Worksheet
     
    Set wbCurrent = ActiveWorkbook
    Workbooks.Add
    Set wbReport = ActiveWorkbook
     
    'копируем на итоговый лист шапку таблицы из первого листа
    wbCurrent.Worksheets(1).Range("A1:D1").Copy Destination:=wbReport.Worksheets(1).Range("A1")
     
    'проходим в цикле по всем листам исходного файла
    For Each ws In wbCurrent.Worksheets
     
        'определяем номер последней строки на текущем листе и на листе сборки
        n = wbReport.Worksheets(1).Range("A1").CurrentRegion.Rows.Count
         
        'задаем исходный диапазон, который надо скопировать с каждого листа - любой вариант на выбор:
        Set rngData = ws.Range("A1:D5")            'фиксированный диапазон или
   
        'копируем исходный диапазон и вставляем в итоговую книгу со следующей строки
        rngData.Copy Destination:=wbReport.Worksheets(1).Cells(n + 1, 1).PasteSpecial (xlPasteValues)
         
    Next ws
End Sub

let   
  fx=(a as text, b as number, delim as text)=>   
    Web.Page(
        "<script>
             x = '"& Text.From (a) &"';
             y = '"& Text.From (b) &"';
             z = '"& Text.From (delim) &"'; 
             arr = x.split(z);
             arrnew = [];
             if (y > 0) {
               for (i = 0; i < arr.length; i++) {
                    if ((y-arr[i]) < 0) {
                       arr[i] = -(y-arr[i]);
                       arrnew.push(arr[i]);
                       break;
                    }  

               y = y-arr[i];
               arr[i] = 0;
               arrnew.push(arr[i]);
               }
            }
            arr.splice(0, arrnew.length);     
            result = arrnew.concat(arr).join(z);
            document.write(result);       
         </script>")
in
fx

let   fx=(a as text, b, delim as text)=>
   
 Web.Page(
        "<script>
             var x = '"& a &"';
             var y = '"& Text.From(b) &"';
             var z = '"& delim &"'; 
             var arr = x.split(z);
             var arrnew = [];
             if (y > 0) {
                for (let i of arr) {
                    if ((y-i) < 0) {
                       i = -(y-i);
                       arrnew.push(i);
                       break;
                    }  

               y = y-i;
               i = 0;
               arrnew.push(i);
               }
            }
            arr.splice(0, arrnew.length);     
            var result = arrnew.concat(arr).join(z);
            document.write(result);       
          </script>")
in
fx

let   fx=(txt as text, regex as text)=>
    Web.Page(
        "<script>
            var x = '" & txt & "';
            var pattern = /" & regex & "/gi;
            var result = x.replace(pattern,"");
            document.write(result);
        </script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0}

in
fx