vba 如果工作表名称已存在，则 Excel 重命名工作表

Excel rename sheet with if sheet name already exists

提问by luke

How can i rename a sheet and add a number to the end of the name if the name already exists.

如果名称已存在，我如何重命名工作表并在名称末尾添加一个数字。

I'm using this code but need to add a number to the end of sheet name if name already exists.

我正在使用此代码，但如果名称已存在，则需要在工作表名称的末尾添加一个数字。

VBA_BlankBidSheet.Copy After:=ActiveSheet
ActiveSheet.Name = "New Name"

回答by Shai Rado

The code below loops through all worksheets in ThisWorkbookand checks if there is already a sheet with a name of "New Name", if it does it adds a number at the end.

下面的代码循环遍历所有工作表，ThisWorkbook并检查是否已经存在名称为“新名称”的工作表，如果存在，则在末尾添加一个数字。

Sub RenameSheet()

Dim Sht                 As Worksheet
Dim NewSht              As Worksheet
Dim VBA_BlankBidSheet   As Worksheet
Dim newShtName          As String

' modify to your sheet's name
Set VBA_BlankBidSheet = Sheets("Sheet1")

VBA_BlankBidSheet.Copy After:=ActiveSheet    
Set NewSht = ActiveSheet

' you can change it to your needs, or add an InputBox to select the Sheet's name
newShtName = "New Name"

For Each Sht In ThisWorkbook.Sheets
    If Sht.Name = "New Name" Then
        newShtName = "New Name" & "_" & ThisWorkbook.Sheets.Count               
    End If
Next Sht

NewSht.Name = newShtName

End Sub

回答by Darren Bartrup-Cook

The test procedure on a new workbook will generate these sheet names: Sheet1_1, Sheet2_1 and ABC.

新工作簿上的测试过程将生成以下工作表名称：Sheet1_1、Sheet2_1 和 ABC。

If Sheet1_1 exists and we ask for a new Sheet1 it will return Sheet1_2, as ABC doesn't exist in a new workbook it will return ABC.

如果 Sheet1_1 存在并且我们要求一个新的 Sheet1，它将返回 Sheet1_2，因为 ABC 在新工作簿中不存在，它将返回 ABC。

The Test code adds a new sheet called 'DEF'. If you run it a second time it will create 'DEF_1'.

测试代码添加了一个名为“DEF”的新表。如果您第二次运行它，它将创建“DEF_1”。

Sub Test()

    Debug.Print RenameSheet("Sheet1")
    Debug.Print RenameSheet("Sheet2")
    Debug.Print RenameSheet("ABC")

    Dim wrkSht As Worksheet
    Set wrkSht = Worksheets.Add
    wrkSht.Name = RenameSheet("DEF")

End Sub

    Public Function RenameSheet(SheetName As String, Optional Book As Workbook) As String

        Dim lCounter As Long
        Dim wrkSht As Worksheet

        If Book Is Nothing Then
            Set Book = ThisWorkbook
        End If

        lCounter = 0
        On Error Resume Next
            Do
                'Try and set a reference to the worksheet.
                Set wrkSht = Book.Worksheets(SheetName & IIf(lCounter > 0, "_" & lCounter, ""))
                If Err.Number <> 0 Then
                    'If an error occurs then the sheet name doesn't exist and we can use it.
                    RenameSheet = SheetName & IIf(lCounter > 0, "_" & lCounter, "")
                    Exit Do
                End If
                Err.Clear
                'If the sheet name does exist increment the counter and try again.
                lCounter = lCounter + 1
            Loop
        On Error GoTo 0

    End Function  

Edit:Removed the Do While bNotExistsas I wasn't checking bNotExists- just using Exit Doinstead.

编辑：删除了，Do While bNotExists因为我没有检查bNotExists- 只是使用Exit Do。

回答by Manu K.

Building on Darren's answer, I thought it might be easier to just rename the sheet right away instead of returning the name that can be used. I also refactored a bit. Here's my take:

基于 Darren 的回答，我认为立即重命名工作表而不是返回可以使用的名称可能会更容易。我也重构了一点。这是我的看法：

Private Sub nameNewSheet(sheetName As String, newSheet As Worksheet)
    Dim named As Boolean, counter As Long
    On Error Resume Next
        'try to name the sheet. If name is already taken, start looping
        newSheet.Name = sheetName
        If Err Then
            If Err.Number = 1004 Then 'name already used
                Err.Clear
            Else 'unexpected error
                GoTo nameNewSheet_Error
            End If
        Else
            Exit Sub
        End If

        named = False
        counter = 1

        Do
            newSheet.Name = sheetName & counter
            If Err Then
                If Err.Number = 1004 Then 'name already used
                    Err.Clear
                    counter = counter + 1 'increment the number until the sheet can be named
                Else 'unexpected error
                    GoTo nameNewSheet_Error
                End If
            Else
                named = True
            End If
        Loop While Not named

        On Error GoTo 0
        Exit Sub

    nameNewSheet_Error:
    'add errorhandler here

End Sub

回答by vinnief

The .net version of VB uses the Try ... Catch formulation to catch runtime errors, see {https://msdn.microsoft.com/en-us/library/ms973849.aspx}(https://msdn.microsoft.com/en-us/library/ms973849.aspx) for a comparison with the old "on error" formulation of VB6 and before. It is better suited to do what you want, and will make shorter exception runs imho.

VB 的 .net 版本使用 Try ... Catch 公式来捕获运行时错误，请参阅 { https://msdn.microsoft.com/en-us/library/ms973849.aspx}( https://msdn.microsoft. com/en-us/library/ms973849.aspx) 与 VB6 及之前的旧“错误”公式进行比较。它更适合做你想做的事，并且会缩短异常运行时间。

I'm trying to find out what exception is thrown when trying to rename to an existing sheetname, and will edit here to a workable script when i find it.

我试图找出在尝试重命名为现有工作表名称时抛出的异常，并在我找到它时将其编辑为一个可行的脚本。