if you need only result in another page then try this
Sub GetAverage()
Dim wbkSource As Workbook
Dim wbkDest As Workbook
Dim rngData As Range
Dim intItem As Integer
Set wbkSource = ThisWorkbook
Set wbkDest = Workbooks.Add
With wbkSource.Worksheets("Sheet1")
Set rngData = Intersect(.Range("A1").CurrentRegion, .Range("A1").CurrentRegion.Offset(1))
If rngData Is Nothing Then Exit Sub
Set rngData = rngData.Resize(, rngData.Columns.Count + 1)
End With
rngData.Columns(rngData.Columns.Count).Value = "=Average(" & rngData.Rows(1).Resize(, rngData.Columns.Count - 1).Address(0, 0) & ")"
rngData.Columns(rngData.Columns.Count).Copy
wbkDest.Worksheets(1).Range("A2").PasteSpecial xlPasteValues
wbkDest.Worksheets(1).Range("A1").Value = "Average_Result"
Application.CutCopyMode = False
rngData.Columns(rngData.Columns.Count).Delete
Set wbkSource = Nothing
Set wbkDest = Nothing
Set rngData = Nothing
intItem = Empty
End Sub
Try this
Dim wbkSource As Workbook
Dim wbkDest As Workbook
Dim rngData As Range
Dim intItem As Integer
Set wbkSource = ThisWorkbook
Set wbkDest = Workbooks.Add
Set rngData = wbkSource.Worksheets("Sheet1").Range("A1").CurrentRegion
Set rngData = rngData.Resize(, rngData.Columns.Count + 1)
rngData.Columns(rngData.Columns.Count).Value = "=Average(" & rngData.Rows(1).Resize(, rngData.Columns.Count - 1).Address(0, 0) & ")"
rngData.Copy
wbkDest.Worksheets(1).Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False
rngData.Columns(rngData.Columns.Count).Delete
Set wbkSource = Nothing
Set wbkDest = Nothing
Set rngData = Nothing
intItem = Empty