Reference another page as variable in formula VBA

[Christian S]

Hi All,

I'm trying to enter the formula =average($B$" & ProductRow & ":$KL$" & ProductRow & ") into cell "Cells(PivotRow, 3) onto my page "Pivot Data". My Line of code is the following

Cells(PivotRow, 3).Formula = "=average($B$" & ProductRow & ":$KL$" & ProductRow & ")"

This works except for the fact that the data i'm trying to average can be on several different pages (For which i have used the variable "CurrentProduct") How do i include this variable sheet name in my formula? I have already tried the following which gave an error message

Cells(PivotRow, 3).Formula = "=average(" & CurrentProduct!$B$" & ProductRow & ":$KL$" & ProductRow & ")"

Many thanks in advance

 

[Fluff]

How about

"=average('" & CurrentProduct & "'!$B$" & ProductRow & ":$KL$" & ProductRow & ")"