The 'Master Sheet' in the workbook is actually called 'APPLIANCE ORDER'.
At the top of this sheet, in column D, there is a button which, when clicked, will fill out row 8 from column G onwards.
I want every sheet/tab name following the 'APPLIANCE ORDER' sheet to be named from this row.
Currently, the spreadsheet has the code from the website in my original post in every sheet module. So, if you click the button, then go to one of the next sheets and click anywhere in one, the tab name will update but this requires the user to manually go and click in the sheet.
Needless to say, you'll have to enable macros...
If you'd like me to upload another copy with my mobile number in it so I can explain over the phone, pelase let me know.
Apologies, I am not great at describing what I see in excel with words...
The 'Empty' sheets are the ones I wish to automatically change the names for.
When you click the button at the top of the 'APPLIANCE ORDER' sheet, it fills row 8 on that sheet with 'KT01', 'KT02', KT'03', etc. Currently I think it stops at KT08 or thereabouts...
So I would want the 'Empty1' tab to rename to 'KT01', 'Empty2' to rename to 'KT02', etc.
Ideally this would be done automatically after clicking the button OR there would be another button next to the one currently in column D in the APPLIANCE ORDER sheet that, once clicked, would change the name of all the sheets where a KTXX had been entered into row 8 of the APPLIANCE ORDER sheet.
For me, you would only ever click the button more than once if you added a new KTXX. So if KT09 was added to the project, you'd click the button to add it to the APPLIANCE ORDER sheet and the 'Empty9' tab would rename to 'KT09'.
Clicking the button the first time will change the name of the sheets from 'EmptyX' to 'KTXX'. Clicking it again shoulodn't (and currently doesn't) do anything to these sheets unless you remove an existing KTXX or add new KTXXs on if these have been added to the workbook in previous tabs.
So you open the workbook for the first time and you create some KTXXs in one of the sheets before APPLIANCE ORDER. You come to APPLIANCE ORDER and click the button once to bring these KTXXs into row 8. If there are 12 KTXXs ('KT01' to 'KT12'), then 'Empty1' to 'Empty12' would have their names changed to 'KT01' to 'KT12'.
If somebody later adds a 'KT13' to the list in one of the tabs before APPLIANCE ORDER, then clicks the button, 'KT13' would appear after 'KT12' in row 8 in APPLIANCE ORDER and 'Empty13' would change name to 'KT13'.
Dim arrList As Object, a As Variant, i As Long, [COLOR=#ff0000]j As Long[/COLOR]
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)
[COLOR=#ff0000]j = Sheets("APPLIANCE ORDER").Index[/COLOR]
For i = 1 To UBound(a)
If a(i, 1) = "K[COLOR=#00ff00]1[/COLOR]tchen" Then arrList.Add CStr(a(i, 2))
[COLOR=#ff0000]For i = 0 To arrList.Count - 1
Sheets(j + i + 1).Name = arrList(i)
Sheets("APPLIANCE ORDER").Range("G8").Resize(1, arrList.Count).Value = arrList.toArray
BUT you need to make sure that ALL of your hidden sheets are moved so that they are before the APPLIANCE ORDER sheet.
Please note the deliberate typo (in green) otherwise the word will get turned into ***