can I search all worksheets in an xls for highest number on load?
 

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?

can I search all worksheets in an xls for highest number on load?
 

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?

can I search all worksheets in an xls for highest number on load?
 

Hi,

thanks for the reply.

I tried to implement this, but when I load the XLS, I get the option to
run the macro but nothing seems to happen.

Maybe I need idiots guide to adding this macro properly to excel?

can I search all worksheets in an xls for highest number on load?
 

The two routines, Auto_Open and MaxColB both go in a normal module.

First test MaxColB and ensure it works. You will probably want to change
Worksheets("Sheet1").Range("A1")
to suit.

The Auto_Open routine should run automatically when the file opens and call
MaxColB, assuming your security settings allow macros.

Save the file with macros and reopen, the Msgbox should pop up confirming
all is working. Once it is, comment or remove the Msgbox line.

Regards,
Peter T


"megoodsen" wrote in message
ups.com...
Hi,

thanks for the reply.

I tried to implement this, but when I load the XLS, I get the option to
run the macro but nothing seems to happen.

Maybe I need idiots guide to adding this macro properly to excel?