Public Sub m()

On Error GoTo ErrorHandler

Dim mSheetsArray As Sheets
Dim sh As Worksheet

With Application
.ScreenUpdating = False
.Calculation = xlManual
.StatusBar = "Sub m()"
End With

Set mSheetsArray = _
Worksheets(Array("Sheet1", "Sheet3"))

For Each sh In mSheetsArray
With sh
.Range("K1").Value = _
"=AVERAGE(" & _
.Range("J2").CurrentRegion.Address _
& ")"
End With
Next

ExitRow:
Set sh = Nothing
Set mSheetsArray = Nothing
With Application
.ScreenUpdating = True
.Calculation = xlAutomatic
.StatusBar = ""
End With
Exit Sub

ErrorHandler:
MsgBox Err.Number & vbNewLine & Err.Description
Resume ExitRow

End Sub


Or:

Public Sub m()

On Error GoTo ErrorHandler

Dim sh As Worksheet
Dim lLastRow As Long

With Application
.ScreenUpdating = False
.Calculation = xlManual
.StatusBar = "Sub m()"
End With

For Each sh In Worksheets

With sh
If .Name < "Summary Sheet" And _
.Name < "Alpha" And _
.Name < "PSA Time Interval" _
And .Name < "Summary" Then

lLastRow = _
.Range("J" & Rows.Count).End(xlUp).Row
.Range("K1").Value = _
"=AVERAGE(" & _
"J2:j" & lLastRow _
& ")"
End If
End With

Next

ExitRow:
Set sh = Nothing
With Application
.ScreenUpdating = True
.Calculation = xlAutomatic
.StatusBar = ""
End With
Exit Sub

ErrorHandler:
MsgBox Err.Number & vbNewLine & Err.Description
Resume ExitRow

End Sub

--
---------------------------
Mauro Gamberini
http://www.riolab.org/

--
---------------------------
Mauro Gamberini
http://www.riolab.org/
"ryguy7272" ha scritto nel messaggio
...
Mid afternoon; got into the office early and still not fully awake here.
I
am trying to loop through all sheets and fine the average for numbers in a
range, which always starts in J2 (has a header) and goes down a variable
number of rows. I want to enter the average in Cell K1. This is what I
have
so far.

Sub Aver()
Dim lastrow As Long

For Each sh In Worksheets
If (sh.Name) < "Summary Sheet" And (sh.Name) < "Alpha" And (sh.Name)
< "PSA Time Interval" And (sh.Name) < "Summary" Then
sh.Activate


With sh
lastrow = .Cells(.Rows.Count, "J").End(xlUp).Row
.Cells(lastrow, "K").Activate
'ActiveCell.Offset(0, 0).Select
Selection.End(xlUp).Select
ActiveCell.FormulaR1C1 = "=average(r2c:)" & lastrow
End With

End If
Next sh

End Sub

My macro is trying to calculate the average in column K, but I want to
base
the average on Column J. Please help.

TIA,
Ryan---