Ideas on how to track weekly trends from multiple files

I used the code below to get all data on the consolidated sheet.

You can run this code every time you add a new week to the file.

Notice you have to make a new pivot table, after you run the code.

Sub integratie_Oeldere_revisted_vs2()

Dim wsTest As Worksheet

'check if sheet "Consolidated" already exist

Const strSheetName As String = "Consolidated"
 
Set wsTest = Nothing
On Error Resume Next
Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
On Error GoTo 0
 
If wsTest Is Nothing Then
    Worksheets.Add.Name = strSheetName
End If

With Sheets("Consolidated")
    .UsedRange.ClearContents
    .Range("A1:H1").Value = Array("sheet", "SKU", "Location", "Quantity", "Price", "Supplier Code", "Status", "Buyer")
    For Each Sh In Sheets
        With Sh
            If .Name <> "Consolidated" Then
                Rng = .Cells.Find("*", , , , xlByRows, xlPrevious).Row - 1
                NR = Sheets("Consolidated").Cells.Find("*", , , , xlByRows, xlPrevious).Row + 1
                If Rng > 0 Then
                    Sheets("Consolidated").Cells(NR, 1).Resize(Rng) = .Name
                    Sheets("Consolidated").Cells(NR, 2).Resize(Rng, 7) = .Range("A4").Resize(Rng, 7).Value
                End If
            End If
        End With
    Next
    .Range("C2:C" & .Rows.Count).SpecialCells(4).EntireRow.Delete
    .Columns("E:E").NumberFormat = "#,##0.00"
    .Columns("A:Z").EntireColumn.AutoFit
End With
End Sub