find max value in multi worksheets

[sau]

I would like to find the max value in a specific range of multi-worksheets in
a workbook.
I try to select the range of mult-worksheets but I am not able to get the
max value for below coding, please advise me what's the problem and how to
fix it.
Thank you

Sub macro()

Dim wks As Worksheet

For Each wks In Worksheets
wks.Select
wks.Range("A1:A10").Select
Next wks
activeCell.select = multiRange

Dim myRange As Range
Set myRange = multiRange
answer = Application.WorksheetFunction.Max(myRange)

Range("f2").Select
ActiveCell.Value = answer

End sub

 

[Bob Phillips]

Sub macro()
Dim wks As Worksheet
Dim answer As Double, totalAnswer As Double

For Each wks In Worksheets
answer = Application.WorksheetFunction.Max(wks.Range("A1:A10"))
If answer > totalAnswer Then
totalAnswer = answer
End If
Next wks

Range("f2").Value = answer

End Sub

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Tom Ogilvy]

Sub GetMax()
Dim s As String, s1 As String
Dim s2 As String, myMax As Double
s = Worksheets(1).Name
s1 = Worksheets(Worksheets.Count).Name
s2 = "'" & s & ":" & s1 & "'!A1:A10"
myMax = Evaluate("Max(" & s2 & ")")
Range("F2").Value = myMax
End Sub

 

[sau]

Would you please tell me how could I locate the "myMax" cell
(activecell.value = myMax) Instead of putting the max value into a cell.

 

[Bob Phillips]

Sub macro()
Dim wks As Worksheet
Dim answer As Double, totalAnswer As Double
Dim rng As Range

For Each wks In Worksheets
answer = Application.Max(wks.Range("A1:A10"))
If answer > totalAnswer Then
totalAnswer = answer
Set rng = Application.Index(wks.Range("A1:A10"), _
Application.Match(answer, wks.Range("A1:A10"), 0))
End If
Next wks

Range("f2").Value = rng.Address(, , , True)

End Sub






--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[sau]

Range("f2").Value = rng.Address(, , , True)
The above coding will show the maxvalue address. That's great!! However, I
hope it could stop at the maxvalue cell. If above coding showing "sheet3!a9",
the cursor will stop at sheet 3 cell a9 (Application.Goto
Reference:="Sheet3!a9")

 

[Tom Ogilvy]

Sub macro()
Dim wks As Worksheet
Dim answer As Double, totalAnswer As Double
Dim rng As Range

For Each wks In Worksheets
answer = Application.Max(wks.Range("A1:A10"))
If answer > totalAnswer Then
totalAnswer = answer
Set rng = Application.Index(wks.Range("A1:A10"), _
Application.Match(answer, wks.Range("A1:A10"), 0))
End If
Next wks

Application.GoTo rng, True

End Sub