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 -

[TABLE="width: 49"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]785005[/TD]
[/TR]
[TR]
[TD="align: right"]785010[/TD]
[/TR]
[TR]
[TD="align: right"]785015[/TD]
[/TR]
[TR]
[TD="align: right"]785020[/TD]
[/TR]
[TR]
[TD="align: right"]785030[/TD]
[/TR]
[TR]
[TD="align: right"]785031[/TD]
[/TR]
[TR]
[TD="align: right"]785050[/TD]
[/TR]
[TR]
[TD="align: right"]785100[/TD]
[/TR]
[TR]
[TD="align: right"]785105[/TD]
[/TR]
[TR]
[TD="align: right"]785150[/TD]
[/TR]
[TR]
[TD="align: right"]785165[/TD]
[/TR]
[TR]
[TD="align: right"]785210[/TD]
[/TR]
[TR]
[TD="align: right"]785230[/TD]
[/TR]
[TR]
[TD="align: right"]785300[/TD]
[/TR]
[TR]
[TD="align: right"]785305[/TD]
[/TR]
[TR]
[TD="align: right"]785320[/TD]
[/TR]
[TR]
[TD="align: right"]785325[/TD]
[/TR]
[TR]
[TD="align: right"]785340[/TD]
[/TR]
[TR]
[TD="align: right"]785350[/TD]
[/TR]
[TR]
[TD="align: right"]785505[/TD]
[/TR]
[TR]
[TD="align: right"]785510[/TD]
[/TR]
[TR]
[TD="align: right"]785610[/TD]
[/TR]
[TR]
[TD="align: right"]785615[/TD]
[/TR]
[TR]
[TD="align: right"]785775[/TD]
[/TR]
[TR]
[TD="align: right"]785810[/TD]
[/TR]
[TR]
[TD="align: right"]786200[/TD]
[/TR]
[TR]
[TD="align: right"]786400[/TD]
[/TR]
[TR]
[TD="align: right"]786300[/TD]
[/TR]
</tbody>[/TABLE]

 

[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!