Get Total Worksheets In Workbook

[KalEl]

Hi. Im completely new to VB, but heres what Im tring to do. I want to get a count of the total worksheets in my workbook. Then I would like cycle through these sheets & compare some cells. Any advise / help is apricated

[JustinLabenne]

You can use a function to return the sheet count

Option Explicit
 
Public Function ShtCount() As Long
    Application.Volatile True
    ShtCount = ActiveWorkbook.Worksheets.Count
End Function

It can be used just like a formula,

Not sure about comparing cells though, Need more info about what you want.

Got some to share?

[KalEl]

Cool, that worked. So now that I have a count of all sheets how can I make them active one at a time to look at values in each sheets cells?

Thanks for your help.

[si_the_geek]

You can actually use a For Each loop, like this:

For Each MySheet IN ActiveWorkbook.Worksheets
'work with the cells here  (MySheet.Name will return the name of the sheet):
      Msgbox MySheet.Cells(1,1).Value, , MySheet.Name
 
    Next MySheet