Code to get Max Value in a perticular cell in ALL Sheets

[Corey]

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

 

[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....


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

 

[Joe]

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

 

[Rick Rothstein \(MVP - VB\)]

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