Check if a sheet exists vba Excel -debugvba.com

check if sheet exists vba excel

Objective

To check if a sheet exists in a workbook or not using VBA Excel.

Approach

In this VBA excel automation, we had a workbook which contained 2 sample worksheets, i.e. Test_1 and Test_2 and our objective was to check if there is a worksheet named as Test_2 exists in this workbook or not. We can see the tabs for both the worksheets in the image below.

 

check if sheet exists vba excel


Code

In the code below we have created a variable called MySheetName, it contained the value “Test_2”, which is the name of the sheet which we want to find in the workbook. Then we loop though each sheet in the workbook. In each iteration of For loop we check if the currently referred sheet has the same name as that of value of variable MySheetName.

If value in variable MySheetName is equal to name of currently referred sheet, then a message box will be displayed, which will confirm that sheet exists, we can see the same in the image below.

Sub CheckSheetExists()

Dim wSheet As Worksheet
Dim MySheetName As String

'Sheet name to be checked
MySheetName = "Test_2"

'Loop through each worksheet
For Each wSheet In ThisWorkbook.Sheets

'If sheet was found then display the message box
If wSheet.Name = MySheetName Then
MsgBox "Sheet " & MySheetName & " Exists"
End If

Next wSheet

End Sub

 

Since worksheet Test_2 exists, a message box is displayed as shown below.

 

check if sheet exists vba excel


Note

  1. We can also get the name of sheet by input box as well.

Reference

https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.name


Post you may like

Format Text : Add subscript , superscript , strike through , change font colour and style