嵌套循环复制重命名工作表报错：运行时错误'1004'名称已占用

遍历无序日期列时，为每个纳税年度复制Template工作表并重命名。单独的纳税年度识别、工作表存在性检查逻辑均可正常运行，但合并循环后，当后续再次出现已创建过的纳税年度日期时，检查循环失效，尝试重复创建同名工作表，触发运行时错误'1004'：That name is already taken. Try a different one.

原VBA代码 #

Option Explicit
Sub Createtaxyears()

'I'm teaching myself so apologies for poor layout/grammar with regards to my code.

Dim cell As Range
Dim Todate As Range

Dim Sort_Table As Worksheet
Dim Template As Worksheet
Dim WSheetfound As Boolean

Dim Template As Worksheet
Dim WSheet As Worksheet

Set Todate = Range("Sorttable[To Date]") 

Dim Tdate As String
Dim M As Variant
Dim I As Variant
Dim Y As Variant

Set Template = Sheets("Template")

Sheets("Sort_Table").Select  'selects source worksheet to start

For Each cell In Todate  'loop to find tax year

    If Not IsEmpty(cell) Then 'avoids runnin git on empty cells
        Tdate = cell.Value
    
        M = Split(Tdate, ".")  'dd.mm.yyyy is not recognised as a date need to use split to ref
        If M(1) >= 5 Then Y = M(2) + 1  ' tax year returns run from May to April
        If M(1) <= 4 Then Y = M(2)      'I'll turn this into a function later
    End If
    
    For Each WSheet In ThisWorkbook.Worksheets
        If WSheet.Name = Y Then
            WSheetfound = True
            
        Else
            WSheetfound = False
        End If

    Next WSheet
   
    If WSheetfound = False Then Template.Copy After:=Sheets(Sheets.Count)
    If WSheetfound = False Then Sheets(Sheets.Count).Select 'selects last sheet to prevent sort_table being renamed.
    If WSheetfound = False Then ActiveSheet.Name = (Y) 
   
Next cell

End Sub

问题根源 #

检查工作表存在性的循环逻辑错误：遍历所有工作表时，只要遇到不是目标名称的工作表，就会将WSheetfound设为False。例如，当2023工作表已存在时，遍历到其他工作表（如Template、Sort_Table）时，会覆盖之前的True状态，最终WSheetfound变成False，导致代码误判工作表不存在，尝试重复创建。

修正后的代码 #

Option Explicit
Sub Createtaxyears()

    Dim cell As Range
    Dim Todate As Range
    Dim Template As Worksheet
    Dim WSheet As Worksheet
    Dim WSheetfound As Boolean
    
    Dim Tdate As String
    Dim M As Variant
    Dim Y As Variant

    Set Todate = Range("Sorttable[To Date]")
    Set Template = Sheets("Template")

    For Each cell In Todate
        WSheetfound = False ' 每次循环前重置检查状态
        If Not IsEmpty(cell) Then
            Tdate = cell.Value
            M = Split(Tdate, ".")
            ' 计算纳税年度（5月至次年4月为一个纳税年度）
            If M(1) >= 5 Then
                Y = M(2) + 1
            Else
                Y = M(2)
            End If
            
            ' 检查工作表是否存在：找到匹配项后立即退出循环
            For Each WSheet In ThisWorkbook.Worksheets
                If WSheet.Name = CStr(Y) Then
                    WSheetfound = True
                    Exit For ' 无需继续遍历，提升效率
                End If
            Next WSheet
            
            ' 仅当工作表不存在时，创建并重命名
            If Not WSheetfound Then
                Template.Copy After:=Sheets(Sheets.Count)
                Sheets(Sheets.Count).Name = CStr(Y) ' 直接操作，避免选中工作表
            End If
        End If
    Next cell

End Sub

关键修正说明 #

• 每次循环开始时重置WSheetfound为False，避免上一次循环的状态残留
• 检查工作表时，找到匹配名称后立即退出循环，防止后续非目标工作表覆盖True状态
• 移除不必要的Select操作，直接操作工作表，提升代码稳定性和执行效率
• 使用CStr(Y)将纳税年度转为字符串，避免数字与工作表名称的类型不匹配问题
• 合并重复的条件判断，简化代码结构，提升可读性

内容的提问来源于stack exchange，提问作者Captain Kriegwurst