Excel VBA loop through worksheets in the same workbook

This tutorial explains how to loop through worksheets in the same workbook, and delete worksheet, add worksheet based on worksheet name

Excel VBA loop through worksheets in the same workbook

Loop though worksheets in the same workbook is useful when combined with If…Else statement. For example, you can loop through worksheets to see which worksheet name contains a text in order to delete it, or add a worksheet if the worksheet doesn’t exist.

VBA Code – loop through worksheets in the same workbook

When you first create a worksheet, the Object is called Sheet1, so is the worksheet name. When you rename a worksheet by right click on the worksheet > Rename, you change the worksheet name, not Object name.

To change the Object name, press Alt+F11, and then change the (Name) Property.

Now we understand that the worksheet name we see is just a property of Object which we loop through.

Below is the standard VBA code to loop through worksheet in the same workbook. Note that we use For Each Loop to loop through Object (because worksheet is an Object) while we use For Loop to loop through variable.

 Sub loopWS()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        'your code
    Next ws
End Sub

The Loop sequence is from the left to the right of the worksheet (not in order of Object list from top to bottom in VBA Project)

Example 1 – loop through worksheet of specific name

The below code loop through worksheet1 to worksheet4, and enter “test” in A1

Sub loopWS()
    For i = 1 To 4
        Sheets("worksheet" & i).Range("A1").Value = "test"
    Next i
End Sub

Example 2 – delete worksheet of specific name

The below code uses Wildcard to check if a worksheet contains “Del”, then delete it.

VBA supports the below three Wildcard characters. Note that Wildcard is case sensitive.

Wildcard	Meaning	Example
*	Represents one or more characters (any character)	J*     any text that starts with J *J     starts with any text but ends with J *J*   any text that has J in the middle
?	Represents one character (any character)	J?     2 characters that start with J ?J     2 characters that end with J ?J?   3 characters with J in the middle
~	Treat * or ? as the actual character but not wildcard. Used ~ followed by * or ?	J~**   any text that starts with J* ~**J   any text that starts with * and ends with J ~?*~* any text that starts with ? and ends with *

Application.DisplayAlerts = False statement is used to disable pop up message to confirm if you really want to delete worksheet.

Sub delWS()
     Application.DisplayAlerts = False
 Dim ws As Worksheet
 For Each ws In ActiveWorkbook.Worksheets
         If ws.Name Like "*Del*" Then
             ws.Delete
         End If
 Next ws
     Application.DisplayAlerts = True
 End Sub

Example 3 – add worksheet if not exist

The below code makes use of a custom Function called wsExist to check if a worksheet already exists, returns TRUE if exists, FALSE if not exist.

Sub createWS()
    Dim ws, newWS As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If Not wsExists("worksheet1") Then
            Set newWS = ThisWorkbook.Worksheets.Add(After:=Worksheets("Sheet8"))
            newWS.Name = "worksheet1"
        End If
    Next ws
End Sub
Function wsExists(wksName As String) As Boolean
    On Error Resume Next
    wsExists = CBool(Len(Worksheets(wksName).Name) > 0)
    On Error GoTo 0
End Function

Excel VBA loop through worksheets in the same workbook