How to Re-name each sheet in workbook with a pre-defined name

How to re-name each sheet in workbook with a pre-defined name

Requirement:

The user has a set of 15 Excel workbooks which are created by our BI system, each workbook is for a different branch of our company, but the 12 worksheets in each book are always the same and in the same order. The problem is that the BI system names each sheet tab with a unique code that doesn't mean anything to us, so the user has to go into each workbook and name the 12 tabs from a list.

My question is, is it possible to do this in VBA as the names for the sheets will always need to be the same.

So, first sheet name needs to be "Group Summary"
second sheet name needs to be "Branch Summary"
third sheet name needs to be "Weekly Trend" ....etc.

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/148358-re-name-each-sheet-in-workbook-with-a-pre-defined-name

Solution:

Code:

Option Explicit
Sub Macro2()

    Dim wsMySheet As Worksheet
    
    Application.ScreenUpdating = False
    
    For Each wsMySheet In ThisWorkbook.Sheets
        wsMySheet.Name = Choose(wsMySheet.Index, "Group Summary", "Branch Summary", "Weekly Trend")
    Next wsMySheet
    
    Application.ScreenUpdating = True
    
    MsgBox "Sheets have now been re-named.", vbInformation

End Sub

Obtained from the OzGrid Help Forum.

Solution provided by Trebor76.