How to re-name each sheet in workbook with a pre-defined name
Category: [Excel] Demo Available
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.
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.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to alternate row colours based on text name |
| How to reference cells without the sheet name |
| How to turn surname and first name around |
| How to merge multiple excel files into one file in separate sheets with source file name |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.