Counting number of sheets that contain a certain name in a workbook

[rosrow31]

Hi,

I have a lot of sheets in a workbook. Within these many sheets, there will be a few sheets with the title "Job Element". I would like to set up cells where I can display "Page 1 of 4" of the Job Element sheets. So, I need to be able to count how many sheets in the workbook contain the text "Job Element". One I have defined that there's say 4 pages containing the title "Job Element", I then need to be able to identify which sheet I am currently on out of those 4 identified.

Just to confirm, there can be more than 4 sheets containing "Job Element", and sheets can be added or removed as required so I need to be able to pick this up automatically.

 

[rondeondo]

Hi Rosrow31
I'd attack this with VBA, using a for loop to cycle through all of the sheets and compare the name with the text you're looking for. Also you'd need a counter and pick a cell in each of these sheets to add a page#. if you want page # of # then you'll need to cycle through twice, once to count them then the second time to label.

this code works. change the line "sh.Cells(1, 1).Value = "Page " & counter & " of " & totalje" to change where you record the page numbers

Sub labelSheets()
Dim counter As Integer
Dim totalje  As Integer

counter = 1
totalje = 0
For step = 1 To 2
    For Each sh In ActiveWorkbook.Sheets
        If LCase(sh.Name) Like "*job element*" Then
            If step = 1 Then 'count total sheets with job elements
                totalje = totalje + 1
            ElseIf step = 2 Then 'add labels
                sh.Cells(1, 1).Value = "Page " & counter & " of " & totalje
                counter = counter + 1
            End If
        End If
    Next sh
Next step
End Sub