Page 4 of 5 FirstFirst ... 2345 LastLast
Results 31 to 40 of 43

Thread: VBA to Make SHeet Name Equal Cell in Sheet
Thanks Thanks: 0 Likes Likes: 0

  1. #31
    New Member
    Join Date
    Sep 2018
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to Make SHeet Name Equal Cell in Sheet

    Yes, absolutely.

    Want me to upload to WeTransfer?

  2. #32
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,120
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: VBA to Make SHeet Name Equal Cell in Sheet

    In that case which line gave the error?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #33
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,120
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: VBA to Make SHeet Name Equal Cell in Sheet

    Also I forgot to change the ******* to uppercase after the board stripped it out, did you make that change?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #34
    New Member
    Join Date
    Sep 2018
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to Make SHeet Name Equal Cell in Sheet

    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 by The_T; Oct 21st, 2019 at 03:49 PM.

  5. #35
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,120
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: VBA to Make SHeet Name Equal Cell in Sheet

    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.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #36
    New Member
    Join Date
    Sep 2018
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to Make SHeet Name Equal Cell in Sheet

    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.

  7. #37
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,120
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: VBA to Make SHeet Name Equal Cell in Sheet

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #38
    New Member
    Join Date
    Sep 2018
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to Make SHeet Name Equal Cell in Sheet

    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!

  9. #39
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,120
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: VBA to Make SHeet Name Equal Cell in Sheet

    My pleasure & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #40
    New Member
    Join Date
    Sep 2018
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to Make SHeet Name Equal Cell in Sheet

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •