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:
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.
|*||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