Here's one way:
Sub x()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
With wks.Range("A1")
If Not SheetExists(.Text, .Parent.Parent) Then
If IsValidSheetName(.Text) Then wks.Name = .Text
End If
End With
Next wks
End Sub
Function SheetExists(sWks As String, Optional wkb As Workbook) As Boolean
On Error Resume Next
SheetExists = Not IIf(wkb Is Nothing, ActiveWorkbook, wkb).Sheets(sWks) Is Nothing
End Function
Function IsValidSheetName(s As String) As Boolean
If Len(s) = 0 Or Len(s) > 31 Then Exit Function
If InStr(s, "\") Then Exit Function
If InStr(s, "/") Then Exit Function
If InStr(s, ":") Then Exit Function
If InStr(s, "|") Then Exit Function
If InStr(s, "*") Then Exit Function
If InStr(s, "?") Then Exit Function
IsValidSheetName = True
End Function
Bookmarks