Check if Sheet Name Exists Before Renaming

[DanGK1]

Hi

I am importing a worksheet from another file and ultimately renaming that imported sheet. I am happy with the import of the sheet and I can format the look and feel but before the sheet is renamed, I would like to check if the planned sheet name exists and if it does append an incremental number to the sheet name. ie if "Functionality" already exists then the newest sheet would be called "Functionality(1)" or "Functionality_1" etc.

This is my existing code and I would assume the check and increment would go in the parts that say activesheet.name

Sub FormatData()

lRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
lCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

With ActiveSheet
.ListObjects.Add(xlSrcRange, Range(Cells(1, 1), Cells(lRow, lCol)), , xlYes).Name = "ISV Data"
.ListObjects("ISV Data").TableStyle = "TableStyleMedium13"
.Columns.AutoFit
.Rows.AutoFit
.Cells.VerticalAlignment = xlTop
End With
NewSheetName = MsgBox("Is this a funcationality import?", vbYesNo + vbQuestion, "Import Type")
If NewSheetName = vbYes Then
ActiveSheet.Name = "ISV Functionality"
Else
ActiveSheet.Name = "ISV Summary"
Range("A1").Select
End If
ImportAnother = MsgBox("Import Another Export?", vbYesNo + vbQuestion, "Import Another?")
If ImportAnother = vbYes Then
Call ImportISVExport
Else
Call CopySheets
End If

End Sub

I would appreciate any guidance.

Thanks

 

[JEC]

This is how you check for an existing sheet

Sub jec()
 If Not IsError(Evaluate("'your sheet name'!A1")) Then
   MsgBox "sheet does exist already"
 Else
   MsgBox "does Not exist"
 End If
End Sub