VBA to Make SHeet Name Equal Cell in Sheet

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,632
Office Version
365
Platform
Windows
In that case which line gave the error?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,632
Office Version
365
Platform
Windows
Also I forgot to change the ******* to uppercase after the board stripped it out, did you make that change?
 

The_T

New Member
Joined
Sep 30, 2018
Messages
31
Haha nope! So I changed it to all capitals and it works when I click the 'run' icon (green play arrow) in Visual Basic but that is the only way it seems to work.

For example, if I copy/paste your code into the sheet module for Empty1, Empty2 & Empty3, then click on the button in column D in APPLIANCE ORDER to bring the KTXXs into row 8, the tabs still have the 'Empty' names.

Is there anyway of getting the code to work without havinbg to go into Virtual Basic and clicking 'run'? I can't seem to get your code to function any other way other than running it in Virtual Basic...
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,632
Office Version
365
Platform
Windows
If you delete the code you had for adding the values to G8 onwards, and then assign the new code to the button, you should be good to go.

You can also delete all the code in the "Empty" sheets, as it's no longer needed.
 

The_T

New Member
Joined
Sep 30, 2018
Messages
31
Fluff,

You are a life-saver!

Thank you for putting up with me all day and responding as quickly and concisely as you have.

This is exactly what I need.

I could stop there but, seeing as we're on a roll, is there any way to make it so that, if you manually delete all the KTXXs from row 8 in APPLIANCE ORDER, then press the button again (or another new button), the KTXX tab names would go back to reading 'EmptyX'?

No worries if not, would just allow the user to conveniently start again should errors be made.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,632
Office Version
365
Platform
Windows
Assign this to another button
Code:
Sub RenameSheets()
    Dim i As Long, j As Long
    j = Sheets("APPLIANCE ORDER").Index
    For i = 1 To Sheets.Count - j
        If Cells(8, i + 6) = "" Then Sheets(i + j).Name = "EMPTY" & i
    Next i
End Sub
 

The_T

New Member
Joined
Sep 30, 2018
Messages
31
You're a hero Fluff.

Thanks a million for your time today.

Don't know what drives you to help somebody out like this for free but thankfully you have!

If you ever need soething translated from French to English, or vice-versa, let me know!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,632
Office Version
365
Platform
Windows
My pleasure & thanks for the feedback
 

The_T

New Member
Joined
Sep 30, 2018
Messages
31
Hi Fluff!

How've you been?

Are you able to help me out again regarding one of the VBA codes you sent me?

I'm finding that this one will only cover so many rows in Column C before it tells me 'subscript out of range':

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("APPLIANCE ORDER").Index
For i = 1 To UBound(a)
If a(i, 1) = "*******" 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("APPLIANCE ORDER").Range("G8").Resize(1, arrList.Count).Value = arrList.toArray
End Sub


Happy to explain further and upload the spreadsheet if you're still about!

Cheers,

T
 

Forum statistics

Threads
1,085,260
Messages
5,382,623
Members
401,795
Latest member
meloanthony

Some videos you may like

This Week's Hot Topics

Top