Automatically Change Sheet Names

[JohnEJo]

Hello. I have been trying to figure out how I can automatically change a sheet name based on the value of a cell on that same sheet along with a text string. For instance, I'm putting the year in cell A3 (in this example 2022), and I want the sheet name to automatically change from Sheet1 to Summary 2022. Like I said, I found examples to change the name to match the value in cell A3, but I cannot figure out how to have it automatically add "Summary " before the value in cell A3. I am guessing it would have to be done in VBA, but I can't figure out how, and any help would be greatly appreciated. Thanks in advance.

 

[sxhall]

Hi JohnEJo

You can use "Summary " & in front of your text string you already have...

Sub ReName()

Dim NewName As String

NewName = Range("A1").Value

Sheets("OldSheet").Name = "Summary " & "NewName"

End Sub

Steven

 

[JohnEJo]

Okay. I tried what you told me but I'm somehow doing something wrong. I'm including a mini-spreadsheet in case that helps. Thanks, again.

Snowden Service Logs 2020.xlsm
	A	B	C	D	E	F	G	H
3	2020
4
5	Vehicle		Last Service Type	Last Service Date	Previous Odomoter	Last Odomoter	Miles Since Last Service	Total Expenses for Year
6
7	2006 GMC Sierra			January 0, 1900				$0.00
8	2015 Chevrolet Silverado			January 0, 1900				$0.00
9	2016 Chevrolet Silverado			January 0, 1900				$0.00
10	2017 Chevrolet Colorado			January 0, 1900				$0.00
11	2004 Chevrolet Duramax			January 0, 1900				$0.00
12	2014 Honda Odyssey			January 0, 1900				$0.00
13
14
Summary 2020

Cell Formulas
Range	Formula
A7:A12	A7	=FILTER(TRANSPOSE(sheetnames),TRANSPOSE(sheetnames)<>"Summary "&A3)
C7	C7	=IFERROR(LOOKUP(2,1/(INDIRECT("'"&A7&"'!h5:h800")<>""),INDIRECT("'"&A7&"'!h5:h800"))&T(NOW()),"")
D7:D14	D7	=IFERROR(MAX(INDIRECT("'"&A7&"'!c5:c800")),"")
E7:E14	E7	=IFERROR(INDEX(INDIRECT("'"&A7&"'!f5:f800"),COUNT(INDIRECT("'"&A7&"'!f5:f800"))-1),"")
F7:F14	F7	=IFERROR(LOOKUP(2,1/(INDIRECT("'"&A7&"'!f5:f800")<>""),INDIRECT("'"&A7&"'!f5:f800"))&T(NOW()),"")
G7:G14	G7	=IFERROR(F7-E7,"")
H7:H14	H7	=IFERROR(SUM(INDIRECT("'"&A7&"'!l5:l800")),"")
C8:C14	C8	=IFERROR(LOOKUP(2,1/(INDIRECT("'"&A8&"'!f5:f800")<>""),INDIRECT("'"&A8&"'!h5:h800"))&T(NOW()),"")
Dynamic array formulas.

 

[sxhall]

Sorry misread your post ref the automatic part!

Paste the below in to the worksheet module (right click on the tab and select view code).

Private Sub Worksheet_Change(ByVal Target As Range)

Dim NewName As String

If Not Intersect(Target, Range("A3")) Is Nothing Then

Sheet1.Name = "Summary " & Range("A3").Value

End If

End Sub

Where I have 'Sheet1' in the above change this to the sheet number next to the name of the sheet. Below my sheet name is (Summary 2025) and it is sheet1.

Now when you change the year in cell A3:H3 it will update the sheet name to 'Summary ' and the year e.g. 'Summary 2025' in my example.

Steven

 

[JohnEJo]

Sorry misread your post ref the automatic part!

Paste the below in to the worksheet module (right click on the tab and select view code).

Private Sub Worksheet_Change(ByVal Target As Range)

Dim NewName As String

If Not Intersect(Target, Range("A3")) Is Nothing Then

Sheet1.Name = "Summary " & Range("A3").Value

End If

End Sub

Where I have 'Sheet1' in the above change this to the sheet number next to the name of the sheet. Below my sheet name is (Summary 2025) and it is sheet1.

View attachment 40427

Now when you change the year in cell A3:H3 it will update the sheet name to 'Summary ' and the year e.g. 'Summary 2025' in my example.

Steven

THANK YOU!! That worked great, exactly as I needed. Thank you very much!