How to check if sheet name exists with VBA

In Excel, you can't have two sheets with same name in the same workbook. If you create a copy of a worksheet, Excel will automatically rename the sheet, usually adding a (1) onto the name. Try creating a copy of an existing sheet and see for yourself. As you add more sheets you probably want to rename them to keep your spreadsheet organized.

There are times when you are using a VBA macro to automate processes in Excel when you want to rename a sheet. If the new name of the sheet is unique your program will be fine. However, if the macro tries to rename a sheet with a name that already exists as another sheet name, you could have a problem. One way to avoid this to make your macro more robust is to add code to loop through all the sheet names and check to see if your new sheet name already exists, and if it does then add something onto the name to make it unique.

Sub CheckSheetNames()

Dim i As Integer

Dim y As Integer

‘the new sheet name, what I want to rename the active sheet as

Dim myName As String

myName = "Sheet"

y = 0

‘loop through all the sheets in the activeworkbook

For i = 1 To ActiveWorkbook.Worksheets.Count

‘if my new sheet name matches any of the existing sheet names, then increase the y count by 1

If myName = Left(ActiveWorkbook.Worksheets.Item(i).Name, 5) Then

y = y + 1

Else

End If

Next

‘If y is greater than 0 then we know that a sheet already with that name already exists

If y > 0 Then

ActiveWorkbook.ActiveSheet.Name = myName & “(“ & y & “)”

Else

‘if y is 0 then the sheet name does not exist so we are free to rename it

ActiveWorkbook.ActiveSheet.Name = myName

End If

End Sub

Now you know how to automatically rename worksheets and how to loop through sheets using VBA. Adding simple checks like these will make your code better and more robust, so if others are using it that don’t have any programming experience they are more likely to not encounter any errors.