Rename multiple tabs based on list

[Dalton Pieters]

Good day please assist,

I have a workbook that has a list from worksheet "Control sheet" cell A2:A459 (dates) that I want to be used to rename all the tabs in the workbook that have already been set up with a template.

unfortunately I am on a restricted network and cannot upload a sample file

Kind regards
Dalton

 

[PCosta87]

Hi @Dalton Pieters

Welcome to the forum

Assuming your "Control sheet" is the first sheet, and all other sheets are ordered as in A2:A459, try the following:

Sub RenameSheets()

    For i = 2 To Worksheets.Count
        Sheets(i).Name = Sheets("Control sheet").Cells(i, 1).Value
    Next i

End Sub

Please see attached

 

[Dalton Pieters]

hi

I am experiencing an "compile error" when I run the code?

could this be because the sheets already have names?

 

[PCosta87]

hi

I am experiencing an "compile error" when I run the code?

could this be because the sheets already have names?

View attachment 36465

The error is because of the "Option explicit"

This forces you to declare every variable and in my example I didn't declare the "i".

To fix just remove "Option explicit" or add "Dim i As Integer" just above the "For i = 2..."

With option explicit:

Option Explicit

Sub RenameSheets()

    Dim i As Integer
   
    For i = 2 To Worksheets.Count
        Sheets(i).Name = Sheets("Control sheet").Cells(i, 1).Value
    Next i

End Sub

 

[Dalton Pieters]

thank you very much for the help

 

[PCosta87]

thank you very much for the help

Don't mention it