Two ways to consider
1. Just use WS formulas, requires some updates as sheets change
2. Use VBA to create formulas
Option Explicit
Sub AveragesFormulas()
Dim aryWorksheets() As String
Dim i As Long
Application.ScreenUpdating = False
'delete old average sheet
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Averages").Delete
Application.DisplayAlerts = True
On Error GoTo 0
'remember worksheets
ReDim aryWorksheets(1 To Worksheets.Count)
For i = LBound(aryWorksheets) To UBound(aryWorksheets)
aryWorksheets(i) = Worksheets(i).Name
Next i
'make new Averages sheet
Worksheets.Add.Name = "Averages"
With Worksheets("Averages")
'headers
Worksheets(aryWorksheets(1)).Cells(1, 1).Resize(1, 5).Copy .Cells(1, 2).Resize(1, 5)
For i = LBound(aryWorksheets) To UBound(aryWorksheets)
'sheet name in col A
.Cells(i + 1, 1).Value = aryWorksheets(i)
'formula in colB
.Cells(i + 1, 2).Formula = "=AVERAGE(" & aryWorksheets(i) & "!A:A)"
Next i
'copy formulas to last 4 col
.Cells(2, 2).Resize(UBound(aryWorksheets), 1).Copy .Cells(2, 3).Resize(UBound(aryWorksheets), 4)
End With
Application.ScreenUpdating = True
End Sub