Excel重命名工作表，如果工作表名称已存在 [英] Excel rename sheet with if sheet name already exists

问题描述

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

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"

推荐答案

下面的代码循环遍历 ThisWorkbook 中的所有工作表，并检查是否已有名称为"New Name"的工作表，如果这样做的话，它会在末尾添加一个数字.

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

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

这篇关于Excel重命名工作表，如果工作表名称已存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！