Sheets("Sheet1").Select
Sheets("Sheet1").Copy Befo=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.

--
Regards,
Tom Ogilvy


"NicB." wrote in
message ...

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 Befo=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.


--
NicB.
------------------------------------------------------------------------
NicB.'s Profile:
http://www.excelforum.com/member.php...o&userid=20639
View this thread: http://www.excelforum.com/showthread...hreadid=497199