VBA to make Sheet Name = Cell Value

[dpc1987]

Is there a vba code to make a sheet name equal a cell value without it being a Private Sub Workbook Change? The reason it can't be private, is because I am taking a summary sheet of a configured part number and copying the sheet within the same workbook. So making it private would cause the first summary sheet to be "ABC" and the copy as "ABC(2)". I already have code to copy the sheet, so I'm looking to add to the code to make the new sheet equal a cell value.

 

[Mike LH]

Hi,

This code will rename the active sheet to whatever is in A1 of the worksheet. If the re-name fails because of illegal characters then a warning is given.

Sub rename_Sheet()
Dim OldName As String
OldName = ActiveSheet.Name
On Error Resume Next
ActiveSheet.Name = Range("A1").Value
On Error GoTo 0
If OldName = ActiveSheet.Name Then
    MsgBox "Worksheet not renamed, Illegal name or no data available"
End If
End Sub

 

[AlphaFrog]

I didn't follow the relevance to the Private Sub Workbook Change comments.

This example renames the sheet using the A1 value

ActiveSheet.Name = ActiveSheet.Range("A1").Value

 

[JLGWhiz]

Is there a vba code to make a sheet name equal a cell value without it being a Private Sub Workbook Change? The reason it can't be private, is because I am taking a summary sheet of a configured part number and copying the sheet within the same workbook. So making it private would cause the first summary sheet to be "ABC" and the copy as "ABC(2)". I already have code to copy the sheet, so I'm looking to add to the code to make the new sheet equal a cell value.

If you copy a sheet within the same workbook, Excel automatically adds a numerical suffix in parentheses, based on how many times you copy the same sheet. ie. first copy "ABC(2)", second 'ABC(3)", etc. Same thing if you copy to a different workbook, Excel still gives it a numerical suffix on the original name. But if you have code like:

Worksheets("ABC").Copy After:=Sheets(Sheets.Count)

Then the copied sheet with the suffix will be the active sheet so to name it using, eg. cell A2, then

ActiveSheet.Name = ActiveSheet.Range("A2").Value

would use the text in cell A2 of the active sheet as the sheet name.

 

[dpc1987]

Mike, your solution worked perfectly. Thank you, and thank you all for responding

 

[yoko007]

Hi Mike
I have the same problem so I used your code. But it works only on one worksheet. How do I make it work on all worksheets in the workbook. The neme in all worksheets is in the same cell (F4) of each worksheet.