VBA Macro Help - Rename sheet tabs based on cell text value

Question

Hi everyone,

I need some macro assistance.

I have the following code:

Sub RenameSheets()

For Each ws In Sheets

ws.Name = ws.Range("A1") 

Next ws

End Sub

The idea is that I want the sheet tab name to change based on the value in A1. I have 40 tabs.

My problem is that have to manually run this code every time. How do I change the value in A1 and the sheet name changes without the manual process?

Thanks,

Chris

Answer 1

In the Visual Basic Editor, double-click ThisWorkbook under Microsoft Excel Objects in the Project Explorer pane on the left hand side.

Copy the following code into the ThisWorkbook module:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Not Intersect(Sh.Range("A1"), Target) Is Nothing Then
        On Error Resume Next
        Sh.Name = Sh.Range("A1").Value
    End If
End Sub

Answer 2

Thanks Hans. I am still running into some issues with the code. I've followed your instructions, and I get this error:

It won't let me run the code.

Thanks,

Chris

Answer 3

The Workbook_SheetChange procedure is not a macro that you can run.

It is a so-called event procedure that will run fully automatically when you enter or edit a value in cell A1 on any of the worksheets.

Try it: select any sheet, and enter a name in cell A1 (it should not coincide with any of the existing sheet names).

After pressing Tab or Enter, you should see the sheet name changing automatically.

Answer 4

Thanks! It worked. Now here is the trickier part of the question. Cell A1 is based on a formula. Is there a way to make that work out.

For example, the formula is simple =Master!A8. I noticed when I change the cell A8 on the Master tab, it'll change A1 on the correct sheet, but the tab name won't change.

Answer 5

I guess I should explain what I am trying to do.

I have one master sheet tab.

The workbook has 40 sheets.

Each sheet is linked to a cell on the Master tab.

All names appear on their respective sheets in Cell A1.

Cell A1 is linked to a cell on the Master tab.

When I change the name, I want it to change the name on the sheet and the sheet tab.

I thought I could do it by adding a formula and the macro would read the text and not be messed up by the formula.

As of right now, it will change the name of the sheet, but not the sheet tab.

Probably easiest explanation is that cell A1 is based on a formula and I need excel to read the text and make that sheet name.