VBA Excel Count Worksheets in a Worknbook

Simply speaking,

Dim L As Long

L = ThisWorkbook.Worksheets.Count

will get you the number of worksheets in the workbook. This counts both visible and hidden sheets.

You can loop through the existing worksheets, visible and hidden, with (at least) four different methods:

Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets

    ' do something with worksheet WS

Next WS

' OR

Dim N As Long

For N = 1 To ThisWorkbook.Worksheets.Count

    ' do soemthing with Worksheets(N)

    Debug.Print ThisWorkbook.Worksheets(N).Name

Next N

' OR

Dim WS As Worksheet

Set WS = Worksheets(1)

Do Until WS Is Nothing

    ' do something with WS

    Debug.Print WS.Name

    Set WS = WS.Next

Loop

' OR

Dim WS As Worksheet

With ThisWorkbook.Worksheets

    Set WS = .Item(.Count)

    Do Until WS Is Nothing

        ' do something with WS

        Set WS = WS.Previous

    Loop

End With

This last proc is essentially the same as the previous proc, except that it reads the sheets right-to-left rather than left-to-right.

Of these three methods, I prefer the For Each loop, but that is mostly a matter of coding style and personal preference.

To work with worksheets whose name matches some sort of pattern, use code like the following. This counts the number of worksheets whose name begins with "X" (upper or lower case).

Dim Count As Long

Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets

    If StrComp(Left(WS.Name, 1), "X", vbTextCompare) = 0 Then

        Count = Count + 1

    End If

Next WS

Take a look at the Like comparison operator to test the name of the sheet against a more complicated text pattern.

If your master sheet is the first sheet, you can loop through the second to final worksheets with code like

Dim N As Long

For N = 2 To ThisWorkbook.Worksheets.Count

    ' do something with Worksheets(N)

Next N

>>>>

 I intend to write the VBA code in the target sheet.

<<<<

If you mean in one of the Sheet modules, I would recommend against that. Put the code in a standard module (Insert menu, Module item) and call it from the Sheet module.

You may have equal or better success with a For Each In... rather than counting the sheets and numerically enumerating them.

Assuming you have a number of worksheets and you want to put the value from A1 from each worksheet into a column in one worksheet called Summary with the name of the source worksheet beside each entry in column B, try this macro,

Sub mcrTotal_from_All_Sheets()

    Dim ws As Worksheet

    For Each ws In Sheets

        If ws.Name <> "Summary" Then

            Sheets("Summary").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = _

              ws.Range("A1").Value

            Sheets("Summary").Cells(Rows.Count, 1).End(xlUp).Offset(0, 1) = _

              ws.Name

        End If

    Next ws

End Sub