Worksheet: Renaming ws to it's default names & delete unwanted worksheet

[inddon]

Hello There,

I have a workbook with 3 worksheets. Each worksheet has it's own defined User Name (eg. Invoices, Journals, Setup, etc.)

I would like to do the following in a single Sub:
1. Define the Worksheet Code and the Worksheet Name in an Array
....(Sheet1 = Invoices, Sheet8 = Journals, Sheet5 = Setup, etc.)
2. Loop through all the worksheets in the workbook:
.....a. Check if the current loop record's, worksheet codename and worksheet name exists in the Array (worksheet codename and worksheet name)
...............If it does not exist then delete the worksheet (eg. Sheet7 = Junk, delete it)
...............If the worksheet codename exists and the name is different as compared in the array, then assign the actual worksheet name from the array to the worksheet
...............(eg. Sheet1 = Blank, then this should be renamed to Invoices)

I managed to write the below code:

Sub wsloop()
   Dim ws As Worksheet
   For Each ws In Worksheets
        MsgBox ws.CodeName & "  " & ws.Name
   Next ws
End Sub

Appreciate your help.

Thank & regards,
Don

 

[Marc L]

Hi !

Without any attachment, as a beginner starter :​

Sub Demo()
        Dim Ws As Worksheet, V, VC, VN
        VC = [{"Sheet1","Sheet5","Sheet8"}]
        VN = [{"Invoices","Setup","Journals"}]
    For Each Ws In Worksheets
            V = Application.Match(Ws.CodeName, VC, 0)
        If IsError(V) Then
            Ws.Delete
        ElseIf Ws.Name <> VN(V) Then
            Ws.Name = VN(V)
        End If
    Next Ws
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[inddon]

Thank you Marc