VBA - Renaming sheets based on specific order of surrounding sheets

[bosshard]

I have several workbooks and I'm wondering how one would go about searching for the sheets that are missing, and the renaming the remaining sheets based on the missing sheets.

In the code below (i know it could be written more cleanly, but im not a dev), code_D_14 refers to an existing sheet. The script removes all of the sheets that contain 'code_D_' as the preceding string, but then I am left without several sheets, one of which is the sheet titled 'code_n_14' (it just goes from code_n_13 to code_n_15 - see image below), leaving a gap in the sheets. Is there a way to rename subsequent sheets that occur after 'code_n_14' with a lower integer, and thus, move all the sheets down in value so that there are no spaces (see attached image).

My issue is that each workbook has a different number of code_n_x sheets, and each has a different number of code_D_x sheets. By the end of the script, for the current example, the number of code_n_x sheets should end at code_n_55 instead of its current form (code_n_60).

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

folderPath = "C:\Users\user\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
 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

 

[dmt32]

Hi,
welcome to forum
you should not need to hard code all your sheet names to do what you want

If it's just the "Code_D" part that determines if sheet should be deleted then you can use the Like operator

Untested but try following

Sub Mymacro()
    Application.DisplayAlerts = False
    For Each Sheet In ActiveWorkbook.Worksheets
         If UCase(Sheet.Name) Like "CODE_D*" Then Sheet.Delete
    Next Sheet
    
    Application.DisplayAlerts = True
End Sub

This should delete any sheet that contains Code_D as first part of its name.

Note: as Like is case sensitive, code makes Tab names in the test, upper case - this does not affect any of the remaining tabs.

Dave

 

[EFANYoutube]

I hope this is what your after as I found this one a little vague but, this will go through all on the worksheets and if it starts with code_D_ or code_n_ it will rename them consecutively
I don't know why you are deleting?

Sub Mymacro()
    Application.DisplayAlerts = False
    Dim DInt, nInt As Integer
    DInt = 1
    nInt = 1
    For Each Sheet In ActiveWorkbook.Worksheets
    
        If Left(Sheet.Name, 7) = "code_D_" Then
            Sheet.Name = "code_D_" & DInt
            DInt = DInt + 1
        End If
        
        If Left(Sheet.Name, 7) = "code_n_" Then
            Sheet.Name = "code_n_" & nInt
            nInt = nInt + 1
        End If
        
    Next Sheet
End Sub

 

[bosshard]

Thanks for all your help! Found a solution which was almost exactly the same as that provided above by EFANYoutube (HUGE THANKS!!):

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

folderPath = "C:\Users\user\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
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