VBA to Make SHeet Name Equal Cell in Sheet

[The_T]

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

 

[Fluff]

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

 

[The_T]

Yes, quite right!

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

Thank you.

T

 

[Fluff]

Glad to help & thanks for the feedback

 

[The_T]

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

 

[MARK858]

Have you tried just removing the

arrList.Sort

line?

 

[The_T]

Have you tried just removing the

arrList.Sort

line?

Mark858 you legend.

Cheers!