• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

inddon

Member
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:

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

Excel Ninja
Hi !

Without any attachment, as a beginner starter :​
Code:
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 !
 
Top