looping thru each w/sheet

[mb97]

Hi,

I'm looking to loop through each ws in the current workbook and, for
each w/sheet, programatically get the highest value in one of the
columns, count the number of occurrences of a cartain variable in
another column and return these values programatically so I can create
a Front Page type of thing.

I think I understand how to get the list of worksheets i'ts after that
I get stuck...I've no idea how to reference cells in a worksheet from
within vba.

also....

is it possible to run the code I create ONLY when a certain cell value
is changed on the Front PAge???

TIA

 

[Bob Phillips]

For Each sh In Activeworkbook.Worksheets
myVar = sh.Range("A1")
myCount = Application.COUNTIF(Columns(3),"abc")
'do something with these values
Next sh

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Dave Peterson]

I think Bob wanted:

myCount = Application.COUNTIF(sh.Columns(3),"abc")

(just to make sure he was using column 3 on sheet sh.)

If the cell changes because of a typing change, then you could tie into the
worksheet_change event.

If you want to read more about these kinds of events:

Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

 

[Bob Phillips]

Thanks Dave.

Bob

(remove nothere from email address if mailing direct)