CODEWITHSUNDEEP
vbaexcel
Curious Lad
Curious Lad

Reputation: 447

Code to be run on multiple specific sheets

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.

Upvotes: 0

Views: 3798

Answers (4)

moffeltje
moffeltje

Reputation: 4658

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

Upvotes: 0

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19737

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

Upvotes: 0

user3364224
user3364224

Reputation: 11

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

Upvotes: 0

Fabrizio
Fabrizio

Reputation: 662

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

Upvotes: 1

Related Questions