Excel VBA - 如何根据丢失的工作表重命名工作表

Question

我刚刚编写了一些代码来遍历文件夹中的所有文件，在工作簿中搜索特定名称的工作表，然后删除所有所说的工作表(见下文 - 我确信有更简洁的编写方式，但我我不是开发人员)。我剩下的是不按顺序排列的工作表(即工作表 1、工作表 2、工作表 4、工作表 5 - 缺少工作表 3)。我想要做的是根据缺少的工作表将所有工作表的名称向下移动，因此上面示例中的 sheet4 将变为 sheet3 等。但工作簿中稍后还会缺少其他工作表(即 sheet40、sheet41、 sheet43 - 缺少工作表 42)，这意味着后面的工作表必须移动 X 个空格，具体取决于缺少的工作表数量。
我认为它起作用的唯一方法是创建应该存在的所有工作表的列表，即 sheet1 到 sheetX，然后在重命名现有工作表时引用此列表。我什至接近吗？

Sub AllFiles()
Dim folderPath As String
Dim filename As String
Dim wb As Workbook

folderPath = "C:\Users\user1\OneDrive\Desktop\something\macro_test\"

If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"

filename = Dir(folderPath & "*.xls")
Do While filename <> ""
  Application.ScreenUpdating = False
    Set wb = Workbooks.Open(folderPath & filename)
     
    'Call a subroutine here to operate on the just-opened workbook
    Call Mymacro
    
    
    filename = Dir
Loop

Application.ScreenUpdating = True
结束子

    Sub Mymacro()
Application.DisplayAlerts = False
For Each Sheet In ActiveWorkbook.Worksheets
     If Sheet.Name = "code_D_1" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_2" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_3" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_4" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_5" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_6" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_7" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_8" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_9" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_10" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_11" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_12" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_13" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_14" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_15" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_16" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_17" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_18" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_19" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_20" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_21" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_22" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_23" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_24" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_25" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_26" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_27" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_28" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_29" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_30" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_31" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_32" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_33" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_34" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_35" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_36" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_37" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_38" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_39" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_40" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_41" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_42" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_43" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_44" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_45" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_46" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_47" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_48" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_49" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_50" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_51" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_52" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_53" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_54" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_55" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_56" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_57" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_58" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_59" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_60" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_61" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_62" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_63" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_64" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_65" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_66" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_67" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_68" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_69" Then
            Sheet.Delete
     ElseIf Sheet.Name = "code_D_70" Then
            Sheet.Delete
     
     End If
Next Sheet
End Sub

Answer 1

考虑以下代码，我试图使其尽可能简单:

Sub delRename()
Dim i As Long, Sheet As Variant
Application.DisplayAlerts = False
i = 1
For Each Sheet In ActiveWorkbook.Worksheets
    If Left(Sheet.Name, 7) = "code_D_" Then
        Sheet.Delete
    ElseIf Left(Sheet.Name, 7) = "code_n_" then
        Sheet.Name = "code_n_" & i
        i = i + 1
    End If
Next Sheet
End Sub

它遍历事件书中的工作表，并使用 Left() 函数删除所有以名称“code_D_”开头的工作表。
对于名为“code_n_”的工作表，它将名称更改为有序。
请注意，如果名为“code_n_X”的工作表出现故障，则可能会失败，因为该名称已被占用。
如果这是一个问题，您必须为此添加检查。