Macro to rename tabs from a control sheet
Results 1 to 7 of 7

Thread: Macro to rename tabs from a control sheet
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2016
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macro to rename tabs from a control sheet

    Hi all,

    Sorry for a n00b question. I have a control sheet and many other sheets that accumulate into the control sheet. But the sheet names always change, so is it possible to create a macro that updates the tabs to reflect the names from the control sheet?

  2. #2
    Board Regular dave3009's Avatar
    Join Date
    Jun 2006
    Location
    Inverclyde, Scotland
    Posts
    6,996
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Macro to rename tabs from a control sheet

    Hi tennisfreak,

    I think, depending on a few things, this can be done. Under what condition do the sheet names 'always change', how do the cells in the control sheet relate to the sheet tabs. Under what conditions would the sheet tabs be reassigned or control sheet updated? Might it be easier just to try preventing the sheets from changing names rather than trying to keep track of when and how the sheets are being renamed?

    Thanks

    Dave
    Please state your version of Excel, I use Excel 2007 on Win 10.
    Back up all data and test VBA code on a COPY workbook, and please use [code][/code] tags.
    HTML Maker | HTML Maker - FAQ

  3. #3
    Board Regular DILIPandey's Avatar
    Join Date
    Jul 2013
    Location
    Dubai
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to rename tabs from a control sheet

    Hi Tennisfreak,

    Yes.. macro can change the sheet names but before that, what is the purpose (reason) because of which these sheet name are getting changed ?
    and then, will it not create a problem when all sheets names will be changed from control sheet ? or you will first update sheet names in control sheet or something like that ?

    Regards,
    DILIPandey

  4. #4
    New Member
    Join Date
    Mar 2016
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to rename tabs from a control sheet

    Thank you both for replying. So here is the background info, every 6 months I have tennis matches (hence the username) at other clubs that rotate. Each match is then recorded on separate sheets and then compiled into the control sheet.
    ex. Cells B1:G1 have all the names of the clubs and I want each cell to relate to the sheet?

    Does any of this make any sense? haha

  5. #5
    Board Regular dave3009's Avatar
    Join Date
    Jun 2006
    Location
    Inverclyde, Scotland
    Posts
    6,996
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Macro to rename tabs from a control sheet

    Hi tennisfreak,

    It sort of makes sense. Though a snapshot of how the layout looks would be very useful so we can assist you finding the exact solution. The thing is if you have a formula on your sheet that contains a reference to another sheet (might look like Wimbeldon!$A$1:$S$50) then if you change the sheet name the formula would update itself. I'm just a little confused as to how you link the Control Sheet cells B1:G1 to each sheet in the book. I'm sure there will be other questions as this grows arms and legs but hopefully with an idea of how it actually looks we might be able to help you set it up so it doesn't get out of hand

    Regards

    Dave

    p.s. there is a link to some useful tools in my signature block.
    Please state your version of Excel, I use Excel 2007 on Win 10.
    Back up all data and test VBA code on a COPY workbook, and please use [code][/code] tags.
    HTML Maker | HTML Maker - FAQ

  6. #6
    New Member
    Join Date
    Mar 2016
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to rename tabs from a control sheet

    I have included a screenshot to illustrate

    So place 1 through 14 appear on the control sheet at the top (cells B1:O1) and i want each cell to be able to change the name of the tab when I edit the cells B1:O1


  7. #7
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,683
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Macro to rename tabs from a control sheet

    Try this:
    To install this code:

    Right-click on the "Control" sheet tab
    Select View Code from the pop-up context menu
    Paste the code in the VBA edit window
    Then as you enter names in Row (1) your sheet names should change.
    Be sure and create the blank sheets before running this script.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B1:O1")) Is Nothing Then
    On Error GoTo J
    Sheets(Target.Column).Name = Target.Value
    End If
    Exit Sub
    J:
    Msgbox "That sheet name already exist or is a invalid name"
    Target.Value = "Error"
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I’m not perfect yet. "Memories are forever"

Some videos you may like

User Tag List

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
  •