Rename multiple sheets using a list

[Jnagra88]

Hi all,

Looking to rename multiple sheets based on a list located in worksheet 'codes' from the range B3:B35...

I hope someone can help!

Many thanks in advance

 

[Norie]

Does the list contain the old and new names for the worksheets?

 

[Jnagra88]

No, it's literally just a list of numbers -

785005
785010
785015
785020
785030
785031
785050
785100
785105
785150
785165
785210
785230
785300
785305
785320
785325
785340
785350
785505
785510
785610
785615
785775
785810
786200
786400
786300

<colgroup><col></colgroup><tbody>
</tbody>

 

[Norie]

So which sheet would be renamed with which number from the list?

 

[Jnagra88]

At present I only have one sheet which is called 'codes' with all those numbers on there. I need new sheets with those numbers if possible

 

[Finalfight40]

Does this work for you?

Sub AddWorkSheets()


For i = 3 To 35


    Sheets.Add After:=Worksheets(Worksheets.Count)
    Sheets(Worksheets.Count).Name = Sheets("Codes").Range("B" & i).Value
Next


End Sub

 

[Norie]

So rather than rename existing sheets you want to add new sheets and use the list for their names?

Try this.

Dim wsCodes As Worksheet
Dim wsNew As Worksheet
Dim cl As Range

    Set wsCodes = Sheets("Codes")

    For Each cl in wsCodes.Range("B3", wsCodes.Range("B" & Rows.Count).End(xlUp)).Cells
        Set wsNew = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        wsNew.Name = cl.Value
    Next cl

 

[Jnagra88]

Sorry, i meant add new sheets with the list of numbers.

The macro has worked, thank you everyone!