Renaming Multiple sheets based on cell value

[wolfmhed]

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 the sheets. I would like to change the tab/sheet name of the 1st group of sheets based on the value of "B6" on the Main page, 2nd group on "B7", 3rd group on "B8" and 4th group on "B9". From 2nd group to 4th group if there are NO value on the cell B7 to B9 on the Main page it will hide the sheets. Thank you in advance for the help!

 

[auto.pilot]

Not exactly what you are looking for, but perhaps this will get you started. This one will rename each sheet based on the value of cell A1 on that sheet.

Sub RenameSheets()
For i = 1 To Sheets.Count
If Worksheets(i).Range("A1").Value <> "" Then
Sheets(i).Name = Worksheets(i).Range("A1").Value
End If
Next
End Sub

 

[wolfmhed]

Thanks for your reply...
I'm trying to start with this code, but it doesn't work. I'm making a code that will rename the sheet by replacing the last 3 character ("GR1") of all the sheets with the value on the sheet1 "A1". I'm totally breaking my head on this. Thank you in advance.

Sub Button1_Click()
Dim Oldname()
Dim NewName As String
Dim i As Long
Oldname = Array("JAN-GR1", "FEB-GR1", "MAR-GR1", "APR-GR1", "MAY-GR1", "JUNE-GR1", "JUL-GR1", "AUG-GR1", "SEP-GR1", "OCT-GR1", "NOV-GR1", "DEC-GR1")
For i = 1 To 12
NewName.Name = Left(Oldname(i), Len(Oldname(i).Name) - 3) & Sheet1.Range("A1").Value
Next i
End Sub

 

[Teeroy]

Try:

Dim Oldname
Dim NewName As String
Dim sht
Dim i As Long
Oldname = Array("JAN-GR1", "FEB-GR1", "MAR-GR1", "APR-GR1", "MAY-GR1", "JUNE-GR1", "JUL-GR1", "AUG-GR1", "SEP-GR1", "OCT-GR1", "NOV-GR1", "DEC-GR1")
For i = LBound(Oldname) To UBound(Oldname)
    NewName = Left(Oldname(i), Len(Oldname(i)) - 3) & Sheet1.Range("A1").Value
    For Each sht In Sheets
        If sht.Name = Oldname(i) Then
            Sheets(Oldname(i)).Name = NewName
        End If
    Next
Next i

 

[wolfmhed]

Many Thanks Teeroy...It works fantastic