Excel VBA loop worksheets in the same workbook

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

You may also want to read:

Loop workbooks in folders and subfolders

Excel VBA select multiple worksheets

Excel VBA loop 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 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 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, hidden is also a property of Object, therefore we can loop through all hidden worksheets as well.

Below is the standard VBA code to loop worksheets 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 worksheets of specific name

The below code loop 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</span>
        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 = Len(Worksheets(wksName).Name) > 0
   On Error GoTo 0
End Function