VBA to Make SHeet Name Equal Cell in Sheet

The_T

New Member
Joined
Sep 30, 2018
Messages
31
Hi everyone,

I used the second VBA code option on the following site to make sheet/tab names equal a cell value within their respective sheets:

https://excel.tips.net/T002145_Dynamic_Worksheet_Tab_Names.html

The problem is, the names do not update automatically (as is claimed on the paged).

Instead, you have to click anywhere in the sheet to make the sheet/tab name update.

So, for example, if I want the sheet/tab name at the bottom of my excel page to equal the value in cell A1, and the value in A1 has been changed, I have to click somewhere on the sheet to update the sheet/tab name and this largely defeats the purpose of the code.

Could somebody please help by providing a VBA code that updates the sheet/tab name as soon as the cell value changes without any manual prompting?

Thanks.

T
 
I suspect that you don't have enough "Empty" sheets.
 
Upvote 0

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Yes, quite right!

You're breadth of excel-related knowledge never ceases to amaze me.

Thank you.

T
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
Fluff!

You still there mate?

This spreadaheet is going down very well at work.

I have a question -

We had a VBA code pulling information from an array through into a Row on a sheet and the data was arranged in alphabetical order. The code was:

Sub Transposing_Column_To_Row()
Dim arrList As Object, a As Variant, i As Long, j As Long
Set arrList = CreateObject("System.Collections.ArrayList")
a = Sheets("1. DRAWING REG").Range("C19:D" & Sheets("1. DRAWING REG").Range("D" & Rows.Count).End(xlUp).Row)
j = Sheets("6. APPLIANCE ORDER").Index
For i = 1 To UBound(a)
If a(i, 1) = "KITCHEN" Then arrList.Add CStr(a(i, 2))
Next
arrList.Sort
For i = 0 To arrList.Count - 1
Sheets(j + i + 1).Name = arrList(i)
Next i
Sheets("6. APPLIANCE ORDER").Range("G8").Resize(1, arrList.Count).Value = arrList.toArray
End Sub

Is there any way of removing the element that puts the data in alphabeitcal order and just keeping it as per what is in the array?

Please let me know ifyou want me to upload the excel file again and detail the query a little more (if you're still about and willing to help me again that is!).

Cheers,

T
 
Upvote 0
Have you tried just removing the
VBA Code:
arrList.Sort
line?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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
Back
Top