VBA - 根据特定顺序重命名工作表

大佬教程收集整理的这篇文章主要介绍了VBA - 根据特定顺序重命名工作表，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。

如何解决VBA - 根据特定顺序重命名工作表？

我有几本工作簿，我想知道如何搜索丢失的工作表，并根据丢失的工作表重命名剩余工作表。

在下面的代码中（我知道它可以写得更干净，但我不是开发人员），code_D_14 指的是现有的工作表。该脚本删除了所有包含“code_D_”作为前面字符串的工作表，但随后我没有几张工作表，其中一张是名为“code_n_14”的工作表（它只是从 code_n_13 到 code_n_15 - 见下图），在床单上留下空隙。有没有办法用较低的整数重命名“code_n_14”之后出现的后续工作表，从而将所有工作表的值向下移动，以便没有空格（见附图）。

Sub Allfiles()
Dim folderPath As String
Dim fil@R_607_8371@ As String
Dim wb As Workbook

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

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

fil@R_607_8371@ = Dir(folderPath & "*.xls")
do while fil@R_607_8371@ <> ""
  Application.Screenupdating = false
    Set wb = Workbooks.Open(folderPath & fileName)
     
    'Call a subroutIne here to operate on the just-opened workbook
    Call Mymacro
    
    fil@R_607_8371@ = Dir
Loop
 Application.Screenupdating = True
 End Sub

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

我的问题是每个工作簿都有不同数量的 code_n_x 工作表，并且每个工作簿都有不同数量的 code_D_x 工作表。在脚本结束时，对于当前示例，code_n_x 工作表的数量应以 code_n_55 而不是其当前形式 (code_n_60) 结束。

解决方法

Sub AllFiles()
    Dim folderPath As String
    Dim fil@R_607_8371@ As String
    Dim wb As Workbook
    
    folderPath = "C:\Users\shAnnonbosshard\OneDrive - Australian Radio Network Pty Limited\Desktop\NeuroconsulTing\macro_test\"
    
    If Right(folderPath,1) <> "\" Then folderPath = folderPath + "\"
    
    fil@R_607_8371@ = Dir(folderPath & "*.xls")
    Do While fil@R_607_8371@ <> ""
      Application.Screenupdating = false
        Set wb = Workbooks.Open(folderPath & fileName)
         
        'Call a subroutIne here to operate on the just-opened workbook
        Call Mymacro
        
        
        fil@R_607_8371@ = Dir
    Loop
    Application.Screenupdating = True
    End Sub
    
    
    Sub Mymacro()
    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
    ActiveWorkbook.Close SaveChanges:=True
    End Sub

大佬总结

以上是大佬教程为你收集整理的VBA - 根据特定顺序重命名工作表全部内容，希望文章能够帮你解决VBA - 根据特定顺序重命名工作表所遇到的程序开发问题。

如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。