Strange Replace Failure

[MichaelDavid]

Greetings! My VBA macro has executed the following instruction correctly
without any problems about 60 times a month for the last year in order to
remove any blanks from the name of the active sheet:

ActiveSheet.Name = Replace(ActiveSheet.Name, " ", "")

This evening when my macro executed this instruction, I could see that it
failed to remove the one blank from the sheet name, and VBA presented me with
the following error message: "Run-time error '1004': Cannot rename a sheet to
the same name as another sheet, a referenced object library or a work book
referenced by Visual Basic"

Does anyone have any ideas as to what might have caused this? Thanks!

--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

 

[Jacob Skaria]

The workbook might have got another sheetname with the same name as the
active sheet (without spaces)
eg:

Sheet 1
Sheet1

If you try to rename the first sheet it will return this error

If this post helps click Yes

 

[MichaelDavid]

Greetings! Fortunately I saved the workbook. And I noticed that there was
already a worksheet with the name my macro was trying to rename this
worksheet as. Somehow my macro must have restarted. This never happened
before.

Nice catch! Thanks a million.
--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

 

[r]

Sub test1()
Dim s As String

s = Replace(ActiveSheet.Name, " ", "")

If WsExist(ActiveWorkbook, s) Then
Err.Raise 1001, , "Cannot rename a sheet"
Else
ActiveSheet.Name = s
End If
End Sub

Function WsExist(Wb As Excel.Workbook, _
WsName As String) As Boolean
On Error Resume Next
WsExist = IsObject(Wb.Worksheets(WsName))
End Function

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html