VBA: Insert formula with variable number of sheets

[FryGirl]

Not sure how to get the right syntax here to represent a variable number of sheets.

Sub TryThis1()
    Range("G2").Formula = "=AVERAGE('1:4'!E2)"
End Sub

The 4 in the formula is the variable part. Whatever number is in Sheets("CtrlPnl").Range("H12") is the total number of sheets. In this example I've just shown 4, but it could be up to 20 or more.

 

[JoeMo]

Maybe:

Sub TryThis1()
Dim NumShts As Long
NumShts = Sheets("CtrlPnl").Range("H12")
Range("G2").Formula = "=AVERAGE('1:" & NumShts & "'!E2)"
End Sub

 

[FryGirl]

Hi Joe,

That is perfect. Thank you.

 

[JoeMo]

Hi Joe,

That is perfect. Thank you.

You are welcome - thanks for the reply.