Code to be run on multiple specific sheets

Question

I want to run a code on multiple sheets. The sheet names are: Sheet1, Sheet2, 1, 2 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 and Summary. I define my sheets of interest= (1, 2, 3, 4 and Summary). The code should run only on these sheets. If any sheet in sheets of interest is not present, it should run for all other sheet of interest, i.e. if 1,2 are not present it should run for 3,4 and Summary.

Answer 1

you can loop each sheets into your workbook

Option Explicit
Dim ws As Worksheet, a As Range
Sub forEachWs()

For Each ws In ActiveWorkbook.Worksheets
    Call yourcode
Next

End Sub

Answer 2

This is a very basic question and you should find the answer by simply Googling it. Though here combined with this is the answer for you.

Sub WorksheetLoop()

     Dim WS_Count As Integer
     Dim I As Integer
     Dim found As Integer
     Dim index As Integer
     Dim sheetnames {"1", "2", "Summary"}



     ' Set WS_Count equal to the number of worksheets in the active
     ' workbook.
     WS_Count = ActiveWorkbook.Worksheets.Count

     ' Begin the loop.
     For I = 1 To WS_Count
         found = 0;

        For index = 0 To numbers.GetUpperBound(0)
            If sheetnames(index) = ActiveWorkbook.Worksheets(I).Name Then
                found = 1
            EndIf
        Next 

        If found = 1 Then

            ' Insert your code here.
            ' The following line shows how to reference a sheet within
            ' the loop by displaying the worksheet name in a dialog box.
            MsgBox ActiveWorkbook.Worksheets(I).Name
        EndIf

     Next I

End Sub

Answer 3

As an alternative to For...Next Loop this will work on all worksheets that are numbered:

Sub AllSheets()

    Dim wrkSht As Worksheet

    For Each wrkSht In ThisWorkbook.Worksheets
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Or any workbook.worksheets reference:                   '
    'For Each wrkSht In ActiveWorkbook.Worksheets            '
    'For Each wrkSht In Workbooks("Book2.xlsx").Worksheets   '
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        If IsNumeric(wrkSht.Name) Then
            'Your code here.
        End If
    Next wrkSht

End Sub

---

As a complete alternative you could put the required sheet names in a range in the workbook, give the range a defined name and use this:

Sub All()

    Dim rCell As Range
    Dim wrkSht As Worksheet

    For Each rCell In Range("MyDefinedSheetNameRange")
        If WorkSheetExists(rCell.Value) Then
            Set wrkSht = ThisWorkbook.Worksheets(rCell.Value)
            'Do stuff with wrksht
        End If
    Next rCell

End Sub

Public Function WorkSheetExists(SheetName As String) As Boolean
    Dim wrkSht As Worksheet
    On Error Resume Next
        Set wrkSht = ThisWorkbook.Worksheets(SheetName)
        WorkSheetExists = (Err.Number = 0)
        Set wrkSht = Nothing
    On Error GoTo 0
End Function

Answer 4

For Each sht In ThisWorkbook.Sheets
    If sht.Name <= 12 Then
     '
     '
     'MsgBox sht.Name
    End If
Next