If we're going to go the macro route, there's no reason not to let the macro simply collate your report for you with no formulas. You simply want the data from row1 on every sheet collated into a report on sheet1? That's probably easiest of all.

Option Explicit

Private Sub Worksheet_Activate()
Dim ws As Worksheet, NR As Long
Application.ScreenUpdating = False
Cells.Clear
NR = 1

    For Each ws In Worksheets
        If ws.Name <> Me.Name Then
            Cells(NR, "A") = ws.Name
            ws.Range("B1:H1").Copy Cells(NR, "B")
            NR = NR + 1
        End If
    Next ws
    
Cells.Columns.AutoFit
Application.ScreenUpdating = True
End Sub
Just delete the macro you were using. Then open the SHEET MODULE for this sheet and insert the macro above into the sheet module. This will cause the macro to run itself every time you bring the sheet up onscreen...which makes it as effective as =A1 formulas.

In case you don't know...the sheet module is listed above the regular modules, open the actual module with the sheetname that is your 'report'. Another way to open it is to right-click on the sheet tab and select VIEW CODE.