Macro to Loop Through All Worksheets in a Workbook

Written by kazamraza no comments
This article contains a Microsoft Visual Basic for Applications macro (Sub procedure) that loops through all the worksheets in the active workbook. This macro also displays the name of each worksheet. Example code 1 

      Sub WorksheetLoop()

         Dim WS_Count As Integer
         Dim I As Integer

         ' 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

            ' 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

         Next I

      End Sub
Example code 2, In this example you can also loop through all of the worksheets in the workbook by using a 'For Each' loop. 

      Sub WorksheetLoop2()

         ' Declare Current as a worksheet object variable.
         Dim Current As Worksheet

         ' Loop through all of the worksheets in the active workbook.
         For Each Current In Worksheets

            ' Insert your code here.
            ' This line displays the worksheet name in a message box.
            MsgBox Current.Name
         Next

      End Sub
Example Code 3, another example (For Each Loop) a very simple approach, I have also add code to control the screen updating: 

Sub WorksheetLoop()
    Dim xSh As Worksheet
    Application.ScreenUpdating = False
    For Each xSh In Worksheets
        xSh.Select
        Call your Macro here
    Next
    Application.ScreenUpdating = True
End Sub
Another sample code is as follows: 

Sub sample_code()
    Dim ws As Worksheet
    For Each ws In Worksheets
        With ws
              'the code to be repeated in all worksheets
        End With
    Next ws
End Sub

.