Find the total number of sheets named CSn

Question

Typically a blank estimation workbook contains 9 sheets before data entry sheet CS1 (which is always present), x number of CS sheets (where x could be 50, 100, 200 etc.) and zero sheets after the last CS sheet. Occasionally users will add sheets before and after the last CS sheet but are not permitted to add sheets between the first and last CS sheet.

The requirement is to refine existing VLOOKUP code (which currently looks at avery sheet in the workbook) to only look at all the CS sheets. Therefore the need to find how many named "CSn" (where n is an integer from 1 to unknown) sheets are within a workbook. This is easy if the CS sheets are at the end of the workbook but not so easy if the user adds other sheets after the last CS sheet.

I can't figure out how to determine the last CS sheet index as the number of CS sheets varies.

Quick code to display number of CS sheets

Public Sub No_of_Sheets()

    intCount = ActiveWorkbook.Sheets.Count      'Find total number of workbook sheets
    intCS1_Index = Sheets("CS1").Index          'CS1 Sheet index
    intCSCount = intCount - (intCS1_Index - 1)  'Find total number of CS sheets
    nonCSSheets = intCount - intCSCount         'Find total number of Non-CS sheets

    MsgBox " CS1 Sheet Index = " & intCS1_Index
    MsgBox " CS Sheet Count = " & intCSCount
    MsgBox " Non CS Sheet Count = " & nonCSSheets

End Sub

Answer 1

Loops through each sheet and tests the name for CS and another digit. Then converts that string to an integer and stores it if it is the larger so you get your max value. Tested working:

Sub FindMaxCS()

    Dim ws1 As Worksheet, MaxInt As Integer

    MaxInt = 0

    For Each ws1 In ActiveWorkbook.Worksheets
        If ws1.Name Like "CS*" Then
            If MaxInt < CInt(Mid(ws1.Name, 3, 99)) Then
                MaxInt = CInt(Mid(ws1.Name, 3, 99))
            End If
        End If
    Next ws1

MsgBox MaxInt

End Sub

Answer 2

This short User Defined Function (or UDF) will return the number of sheets that start with the letters CS.

Public Function num_CSx_Sheets()
    Dim w As Long, cs As Long
    For w = 1 To Sheets.Count
        cs = cs - CBool(UCase(Left(Sheets(w).Name, 2)) = "CS")
    Next w
    num_CSx_Sheets = cs
End Function

That can be used on a worksheet like,

=num_CSx_Sheets()

... or in VBA like,

Dim i as long
i = num_CSx_Sheets()