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).
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).
VBA Code:
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