Copy Sheet Formula Reference

[NicB.]

I have a stumper that I have searched this forum for, but to no avail.
I have code that copies an existing sheet and renames that sheet based
upon a value in a cell. I then have another sheet that needs to have a
MAX formula generated based upon the values of the sheet I just renamed.
What VBA code do I use so that the MAX formula references the newly
named sheet?

Here is what I have tried:


Sheets("Sheet1").Select
Sheets("Sheet1").Copy Before:=Sheets(1)
Sheets("Sheet1 (2)").Select
ActiveSheet.Name = ActiveSheet.Range("A1")
Dim shtName As String
Range("B2").Select
ActiveCell.FormulaR1C1 = "=MAX(String!RC[-11]:R[94]C[-11])"

I need that max formula to change based upon the generated sheet name,
help!!!

Thanks,

NicB.

 

[Tom Ogilvy]

Sheets("Sheet1").Select
Sheets("Sheet1").Copy Before:=Sheets(1)
Sheets("Sheet1 (2)").Select
ActiveSheet.Name = ActiveSheet.Range("A1")
Dim shtName As String
shtName = ActiveSheet.Name
Range("B2").Select
ActiveCell.FormulaR1C1 = "=MAX('" & ShtName & _
"'!RC[-11]:R[94]C[-11])"

But based on the fact that your code shows the formula on the same sheet as
the range being looked at, you don't need a sheet name.

Also, if you are putting the formula in B2, then using a relative reference
of -11 columns shouldn't work.