Rename multiple sheets based on sheets number and cell value

[priisha]

Dear all,

I'm trying to create a macro for my excel file where I want it to rename sheets name based on a certain range in one sheets and each cell value will refer to a specific sheet number

Attached an example excel file

I want to use the value in the sheet ProjectInfo range between B20:B29
The sheet I want to rename are the one yellow marked and starts with Prx.
Note! The sheets are created no in order which mean that one PRx file might be sheet2 and another one might be sheet 4

I've looked around and found this thread to change multiple sheet from range however it changes all sheets in the workbook and it does not work with my purpose

Renaming Multiple sheets based on cell value

Hi, I am seeking for VBA code which will automatically rename the sheets name and hide the unused sheets. I've an workbook that contains 41 sheets and it's divided into 4 groups (each group compose of 10 worksheet) and the workbook has a Main page that contain all the data which are link to...

www.mrexcel.com

please help me

 

[Trebor76]

Hi priisha,

Try this:

Option Explicit
Sub Macro1()

    Dim clnNewSheetNames As New Collection
    Dim rngCell As Range
    Dim ws As Worksheet
    Dim i As Long
    
    Application.ScreenUpdating = False

    For Each rngCell In Sheets("ProjectInfo").Range("B20:B24")
        clnNewSheetNames.Add CStr(rngCell)
    Next rngCell
    
    For Each ws In ThisWorkbook.Sheets
        If StrConv(Left(ws.Name, 3), vbUpperCase) = "PRX" Then
            i = i + 1
            ws.Name = CStr(clnNewSheetNames(i))
        End If
    Next ws
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[priisha]

thanks for your help, I figured another vba code that worked but learned something new with your code

 

[Trebor76]

thanks for your help, I figured another vba code that worked but learned something new with your code

Glad to have helped