Make formula independent of worksheet name

[confusion123]

My code below performs a calculation correctly but if the user changes the name of the worksheet, it will fail.

Cells(2, 2).FormulaR1C1 = "=MAX(Apples!C,Oranges!RC[-1])"

I want to make it specific to the codename of the worksheet, the codename for Apples is wksApples

How can I change it?

Thanks

 

[RoryA]

You can retrieve the tab name using the codename:

Cells(2, 2).FormulaR1C1 = "=MAX('" & wksapples.name & "'!C,Oranges!RC[-1])"

You can do the same with Oranges.

 

[confusion123]

Thanks