VBA to Make SHeet Name Equal Cell in Sheet

The_T

New Member
Joined
Sep 30, 2018
Messages
31
I'm afraid it doesn't seem to do anything.

Is there a way I can send you my excel sheet + an axplanation?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,637
Office Version
365
Platform
Windows
You can upload the file to a share site, such as OneDrive, GoogleDrive, DropBox. Mark it for sharing & then post the link to the thread.
 

The_T

New Member
Joined
Sep 30, 2018
Messages
31
I have uploaded it to WeTransfer --> https://we.tl/t-9rn7w3duO6

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.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,637
Office Version
365
Platform
Windows
I'm afraid it doesn't seem to do anything.
That is because the description you gave & therefore what I suggested, bear absolutely no resemblance to your workbook.
It is always better to give accurate details, rather than generalities.

Will the "Empty" sheets always be called Empty1, Empty2 etc when you run the macro to rename them?
 

The_T

New Member
Joined
Sep 30, 2018
Messages
31
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.

Does that make sense? I am trying my best. :(
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,637
Office Version
365
Platform
Windows
Yup, I understand that, but the next time you click the Button, will you still have sheets labeled KT01,KT02 etc, or will they be back to Empty1, Empty2 etc?

Or is it safe to simply rename the first sheet after APPLIANCE ORDER, then the 2nd then the 3rd etc?
 
Last edited:

The_T

New Member
Joined
Sep 30, 2018
Messages
31
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'.

Does that answer the question?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,637
Office Version
365
Platform
Windows
It does yes, try this mod to your existing macro
Code:
Sub Transposing_Column_To_Row()
  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))
  Next
  arrList.Sort
  [COLOR=#ff0000]For i = 0 To arrList.Count - 1
    Sheets(j + i + 1).Name = arrList(i)
  Next i[/COLOR]
  Sheets("APPLIANCE ORDER").Range("G8").Resize(1, arrList.Count).Value = arrList.toArray
End Sub
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 ***
 
Last edited:

The_T

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

I put the 'AUTOCAD' sheet before the APPLIANCE ORDER sheet (I believe that was the only one between APPLIANCE ORDER and the EMPTIES).

I copy/pasted your code into the sheet module for 'Empty1' in Virtual Basic (only changing the '1' to an 'i' as per the green above).

All that is happening when I click the 'run' icon (the little green 'play' symbol in Virtul Basic') is that I get an error:

- 'Run-time error 1004': Application-defined or object-defined error.

Should I upload the excel workbook with your code in the sheet module for 'Empty1' so that you can see what I'm encountering?
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,637
Office Version
365
Platform
Windows
Did you change the typo I flagged in green to an i from a 1?
 

Forum statistics

Threads
1,085,277
Messages
5,382,684
Members
401,798
Latest member
klikeras

Some videos you may like

This Week's Hot Topics

Top