Sub Auto_Open()
MaxColB

End Sub

Sub MaxColB()
Dim n As Long
Dim mx As Double
Dim ws As Worksheet

ReDim va(1 To Worksheets.Count)

For Each ws In ThisWorkbook.Worksheets
n = n + 1
va(n) = Application.Max(ws.Range("B:B"))
Next

mx = Application.Max(va)

MsgBox mx

Worksheets("Sheet1").Range("A1") = mx

End Sub

Regards,
Peter T

PS Your question about is a new row added is not clear


wrote in message
oups.com...
Hi,

I have a massive list spread over many worksheets in a single XLS file.

There is a field, always in column B, that contains an alpha numeric
number.

What I could really do with is a macro (I guess) that when you load the
file, looks though all records in the B column of all worksheets and
can report to another field somewhere what the greatest number
currently existing in all column B's is.

Is this possible, and if so, could someone offer advice on how please?

thanks

PS, if this IS do-able and is a fairly easy thing, can it also check
again if a new row is added onto a worksheet?