I add sheets to a workbook sometimes on a daily basis and therefore do not know the last sheet
number or name until it has been created.

I need to gain the MAX value in cell J59 in ALL sheets, without having to NAME or KNOW the LAST
sheet, so if this changes i still get the correct MAX value.

Is there a line of code i can use for this ?

Corey....

Think i have a code that will work with a SLIGHT modification:
See arrow below in code:

Sub Workbook_Info()
On Error Resume Next
With Sheets("1") <==== How can i change THIS to ALL SHEETS in workbook INSTEAD
..Select
ActiveSheet.Unprotect
Range("R59").Select
ActiveCell.FormulaR1C1 = "=MAX('Enter-Exit Page:" & Worksheets(Worksheets.Count).Name &
"'!R[0]C[-8])"
End With
ActiveSheet.protect DrawingObjects:=True, Contents:=True, Scenarios:=True,
AllowFormattingCells:=True
End Sub



Corey....


"Corey" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
I add sheets to a workbook sometimes on a daily basis and therefore do not know the last sheet
number or name until it has been created.

I need to gain the MAX value in cell J59 in ALL sheets, without having to NAME or KNOW the LAST
sheet, so if this changes i still get the correct MAX value.

Is there a line of code i can use for this ?

Corey....

On Jan 21, 8:40*am, "Corey" <M...@WorkAgain.com.au> wrote:
> I add sheets to a workbook sometimes on a daily basis and therefore do notknow the last sheet
> number or name until it has been created.
>
> I need to gain the MAX value in cell J59 in ALL sheets, without having to NAME or KNOW the LAST
> sheet, so if this changes i still get the correct MAX value.
>
> Is there a line of code i can use for this ?
>
> Corey....


Dim Max as variant
Dim n As Single
For n = 1 To Sheets.Count
If n=1 then
Max = Worksheets(n).Range("J59")
Elseif Max < Worksheets(n).Range("J59")
Max = Worksheets(n).Range("J59")
Endif
Next n


HTH
Joe

If you run this code, the variable MaxValue will contain the maximum value
from all the R59 cells across all the worksheets (as shown by the MsgBox
statement at the end of the code)...

Sub MaxAcrossSheets()
Dim X As Long
Dim MaxValue As Double
Dim SheetName As String
MaxValue = Worksheets(1).Range("R29").Value
SheetName = Worksheets(1).Name
For X = 2 To Worksheets.Count
If Worksheets(X).Range("R29").Value > MaxValue Then
MaxValue = Worksheets(X).Range("R29").Value
SheetName = Worksheets(X).Name
End If
Next
MsgBox "Maximum Value: " & MaxValue & vbNewLine & _
"Sheet Name: " & SheetName
End Sub

Rick


"Corey" <(E-Mail Removed)> wrote in message
news:uDXJGF%(E-Mail Removed)...
> Think i have a code that will work with a SLIGHT modification:
> See arrow below in code:
>
> Sub Workbook_Info()
> On Error Resume Next
> With Sheets("1") <==== How can i change THIS to ALL SHEETS in workbook
> INSTEAD
> .Select
> ActiveSheet.Unprotect
> Range("R59").Select
> ActiveCell.FormulaR1C1 = "=MAX('Enter-Exit Page:" &
> Worksheets(Worksheets.Count).Name &
> "'!R[0]C[-8])"
> End With
> ActiveSheet.protect DrawingObjects:=True, Contents:=True,
> Scenarios:=True,
> AllowFormattingCells:=True
> End Sub
>
>
>
> Corey....
>
>
> "Corey" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> I add sheets to a workbook sometimes on a daily basis and therefore do not
> know the last sheet
> number or name until it has been created.
>
> I need to gain the MAX value in cell J59 in ALL sheets, without having to
> NAME or KNOW the LAST
> sheet, so if this changes i still get the correct MAX value.
>
> Is there a line of code i can use for this ?
>
> Corey....
>
>
>