Rename Worksheet cell value in another worksheet

[dsrt16]

I have renamed worksheets according to a cell value in the same worksheet, but now I need to rename the worksheet according to a cell value in another worksheet.

Here is the code I tried:

Private Sub Worksheet_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sheets("Set-Up Page").Range("B2").Value <> "" Then
        'rename the worksheet to the contents of cell B2
        Sh.Name = Sheets("Set-up Page").Range("B2").Value
    End If

End Sub

I have pasted that code into the worksheet that needs to be renamed, but it doesn't work. I have also tried:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = Sheets("Set-up Page").Range("B2") Then Sh.Name = Target
End Sub

I like the first one because it checks for a blank value. There will be blank values depending on how many worksheets each teacher needs, so it will just rename the ones that have values filled in. Each worksheet will have the code referencing to a different cell on the Set-Up Page worksheet.

 

[edwincastellino]

Hi Dave,

I need your help with a macro which populates all sheet names in a a series of cells along with their Headers (right,centre,left) and footers(right,centre,left)

Eg - If a workbook has multiple sheets (eg 5 sheets with names - all / ball / call / tall / mall then the output in the "Summary" sheet should show as
A3 = all - B3 = right header contents - C3 = centre header contents - D3 = Left Header contents
A4 = ball - B4 = right header contents
A5 = call -B5
A6 = tall - B6
A7 = mall - B7

Kind regards

 

[Jon von der Heyden]

Hi Edwin

Please pursue your own thread. This thread is intended for someone elses problem.

Thanks

P.S I advise against posting your email address on the board because it is likely to be flagged by spam bots and you'll start to receive a lot of spam in your mailbox.

 

[thomaslovell]

Hi Dave,

I have stumbled across this thread after much searching to find a solution for renaming worksheets. From the description of what the code is designed for, this should work perfectly for me. Similar to dsrt16 I require my worksheets to be renamed by a range of cells in the 'Set-Up Page'.

I have copied and pasted your code into the code window for the 'Set-Up Page', however the following message continues to pop up: "This code assumes the Set-up sheet is the first worksheet, please reorder sheets and retry".

I have tried moving the 'Set-Up Page' to the far left (and right right) to try and make it the first worksheet however the message continues to pop up.

Is there something I am missing? I would really appreciate your advice.. if I can get this macro to work it would be perfect for my spreadsheet!!!

Regards