How to count number of named sheets.

[Dunk4Divin]

Hi All

I have a workbook in which the tool bar and tabs are hidden when the workbook is being used by the user. It has an "Operators Instruction" sheet. This format is used for any number of sheets as required (depending on the part being manufactured). I have written code which duplicates the sheet and removes data images and shapes as necessary. The “master” sheet Tab is named "Gauge Instruction (1)" and each subsequent sheet has the Tab name plus the suffix number (x) as per normal

All sheets must have the information in the header “Gauge Instruction page X of Y pages”. I have the “X” from the current sheet name but not the “Y”. How can I count the number of sheets that have a Tab name starting “Gauge Instruction”.

I've tried using the Name Manager; naming the sheets "GIS" with "Refer To" as =GET.WORKBOOK(1)&T(NOW()) and this formula: =COUNTA(INDEX(GIS,0)) in a cell to count them but it fails to work am I missing something?

Any help will be most welcome!

Thanks

 

[Legal Tender]

I might be oversimplifying the problem but since you seem to want a formula to count the number of Gauge Instruction sheets, the function below will count the number of workbooks in the file that end with ")"

Function s_count() As Integer
s_count = 0
For Each sh In ActiveWorkbook.Sheets
    If Right(sh.Name, 1) = ")" Then
        s_count = s_count + 1
    End If
Next sh
End Function

So once you have that function in a module you could then put "=s_count()" into a cell in the workbook to get the count of the number of sheets whose name ends in ")". Does that work for you?

It could be modified to specifically count the number of sheets that begin with "Gauge Instruction" if that would be more accurate (in case you have other tabs that end with ")".

 

[Dunk4Divin]

Thanks for that. It worked perfectly first time.

Thanks again