Formula to count occurrences across various sheets

[xtremca]

I'm getting my butt kicked trying to create a formula to count instances across various worksheets.

I have a macro that inserts a clean template worksheet between a "First" and "Last" worksheet tabs. This template worksheet is then renamed from "Template (2) or (3....) based on the user input. Cells A9:A36 of these inserted templates have a drop down list of issues for the user to select.

On separate tab, I want to count the occurrences for each of these drop down items. The issue that I'm having is how to count them when I don't have a predefined tab name for these inserted templates. I'm not able to pre-name these inserted tabs because they have to be named after a job number.

Is there a way to write a formula that can read between the first and last tabs, and not specific to a named worksheet?

 

[Roderick_E]

How about this approach. Even though the tabs aren't being named, do they all at least share a unique-shared header somewhere that would distinguish them from another tab that wouldn't be included in your count? If so, you could look through every tab/sheet and look for this unique-shared header and count it.

Something like:

Sub WorksheetLoop2()


         ' Declare Current as a worksheet object variable.
         Dim Current As Worksheet
dim cnt as long
dim findstr as string
findstr = some unique-shared header 'set to whatever


         ' Loop through all of the worksheets in the active workbook.
         For Each Current In Worksheets


            ' Insert your code here.
on error resume next
findit = "" 'reset
findit = Current.Cells.Find(What:=findstr, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
        MatchCase:=False, SearchFormat:=False).Address

           if findit <> "" then
cnt = cnt + 1
endif
resume
         Next
msgbox cnt 'do something with the cnt
      End Sub