Sunday, February 26, 2017

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

End If


‘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 & “)”

‘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.