Automatically Change Sheet Names

JohnEJo

New Member
Joined
Jun 8, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

sxhall

Board Regular
Joined
Jan 5, 2005
Messages
146
Hi JohnEJo

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

Excel Formula:
Sub ReName()

Dim NewName As String

NewName = Range("A1").Value

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

End Sub

Steven
 

JohnEJo

New Member
Joined
Jun 8, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGH
32020
4
5VehicleLast Service TypeLast Service DatePrevious OdomoterLast OdomoterMiles Since Last ServiceTotal Expenses for Year
6
72006 GMC Sierra January 0, 1900   $0.00
82015 Chevrolet Silverado January 0, 1900   $0.00
92016 Chevrolet Silverado January 0, 1900   $0.00
102017 Chevrolet Colorado January 0, 1900   $0.00
112004 Chevrolet Duramax January 0, 1900   $0.00
122014 Honda Odyssey January 0, 1900   $0.00
13      
14      
Summary 2020
Cell Formulas
RangeFormula
A7:A12A7=FILTER(TRANSPOSE(sheetnames),TRANSPOSE(sheetnames)<>"Summary "&A3)
C7C7=IFERROR(LOOKUP(2,1/(INDIRECT("'"&A7&"'!h5:h800")<>""),INDIRECT("'"&A7&"'!h5:h800"))&T(NOW()),"")
D7:D14D7=IFERROR(MAX(INDIRECT("'"&A7&"'!c5:c800")),"")
E7:E14E7=IFERROR(INDEX(INDIRECT("'"&A7&"'!f5:f800"),COUNT(INDIRECT("'"&A7&"'!f5:f800"))-1),"")
F7:F14F7=IFERROR(LOOKUP(2,1/(INDIRECT("'"&A7&"'!f5:f800")<>""),INDIRECT("'"&A7&"'!f5:f800"))&T(NOW()),"")
G7:G14G7=IFERROR(F7-E7,"")
H7:H14H7=IFERROR(SUM(INDIRECT("'"&A7&"'!l5:l800")),"")
C8:C14C8=IFERROR(LOOKUP(2,1/(INDIRECT("'"&A8&"'!f5:f800")<>""),INDIRECT("'"&A8&"'!h5:h800"))&T(NOW()),"")
Dynamic array formulas.
 

sxhall

Board Regular
Joined
Jan 5, 2005
Messages
146
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).

VBA 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.

1623190962103.png


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

New Member
Joined
Jun 8, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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).

VBA 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! :)
 

Forum statistics

Threads
1,136,868
Messages
5,678,226
Members
419,754
Latest member
Sallylwy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top