UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Rename A Sheet On Error, Office 2010    
 
   
wornout
post May 6 2019, 09:40 PM
Post#1



Posts: 1,300
Joined: 17-November 13
From: Orewa New Zealand


I have the below code but if I try and do a sheet with the same name it gives me an error name already found/used
I would like to have a pop up where I can rename it
CODE
Dim xRngAddress As String
Dim xName As String
xRngAddress = ActiveSheet.Range("A2").Address
For Each xWs In Application.ActiveWorkbook.Sheets
    xName = xWs.Range(xRngAddress).Value
    If xName <> "" Then
        xWs.Name = xName
    End If
Next
Go to the top of the page
 
June7
post May 7 2019, 12:51 AM
Post#2



Posts: 618
Joined: 25-January 16



The error message I get is "Run-time error 1004: Application-defined or object-defined error". Quick fix:

CODE
    If xName <> "" Then
        On Error Resume Next
        xWs.Name = xName
        If Err.Number = 1004 Then
            xWs.Name = InputBox("Enter sheet name", "Enter Sheet Name")
        End If
    End If


--------------------
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
dflak
post May 7 2019, 02:00 PM
Post#3


Utter Access VIP
Posts: 6,283
Joined: 22-June 04
From: North Carolina


I found a copy of this wheel at: https://stackoverflow.com/questions/6688131...if-sheet-exists.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    26th June 2019 - 03:00 AM