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" wrote in message
...
Would you please tell me how could I locate the "myMax" cell
(activecell.value = myMax) Instead of putting the max value into a cell.

"Tom Ogilvy" wrote:

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


--
Regards,
Tom Ogilvy

"sau" wrote in message
...
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