Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 24

Thread: Worksheet named from cell

  1. #1
    Acolyte AndyDuncombe's Avatar
    Join Date
    May 2016
    Posts
    24
    Articles
    0
    Excel Version
    365

    Worksheet named from cell



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi
    So I'm trying to help out at work where we use Excel to roster our staff.
    In a workbook we will have 52 tabs (worksheets) for 52 weeks. Is there a way I can name each different tab by calling from a cell - this might be from a list held in another tab?
    I'm really sorry if this has been asked before!

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,938
    Articles
    0
    Excel Version
    O365
    Andy,

    This seems like a simple VBA naming exercise, but I am not sure what you mean by ... 'name each different tab by calling from a cell'. Can you calrify?

  3. #3
    Acolyte AndyDuncombe's Avatar
    Join Date
    May 2016
    Posts
    24
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by Bob Phillips View Post
    Andy,

    This seems like a simple VBA naming exercise, but I am not sure what you mean by ... 'name each different tab by calling from a cell'. Can you calrify?
    I've attached the spreadsheet. The worksheet "SHEET NAMES" and column B is the data source to name sheets 1 - 52. If there are format issues the column C is acceptable
    Attached Files Attached Files

  4. #4
    Acolyte AndyDuncombe's Avatar
    Join Date
    May 2016
    Posts
    24
    Articles
    0
    Excel Version
    365
    HI Bob Phillips;47096 - any luck with this? Feel free to drop me a line on andyduncombe@aol.com

  5. #5
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    832
    Articles
    0
    Excel Version
    Excel 2010
    This in a standard module should work
    Code:
    Sub NameThoseSheets()
        Dim ws As Worksheet
        Dim i As Long
        
    For i = 1 To 52
        Set ws = Sheets("Sheet" & i)
        If Not ws Is Nothing Then
            With ws
                .Name = Sheets("SHEET NAMES").Cells(i, 2).Text
                .Cells(1) = Sheets("SHEET NAMES").Cells(i, 2).Value
            End With
        End If
    Next i
    
    End Sub
    but my Excel 2010 wouldn't load your file without extensive repair, so who knows ???

  6. #6
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,938
    Articles
    0
    Excel Version
    O365
    Sorry about the tardiness Andy, but try this

    Code:
    Public Sub NameThoseSheets()Dim ws As Worksheet
    Dim i As Long
        
        For Each ws In ThisWorkbook.Worksheets
            
            If ws.Name Like "Sheet*" Then
            
                ws.Name = Worksheets("SHEET NAMES").Cells(Mid(ws.Name, 6, 2), "A").Value
            End If
        Next ws
        
        MsgBox "All done"
    End Sub
    PS Hope Pompey have a good second half
    Last edited by Bob Phillips; 2022-01-07 at 05:46 PM.

  7. #7
    Acolyte AndyDuncombe's Avatar
    Join Date
    May 2016
    Posts
    24
    Articles
    0
    Excel Version
    365
    So do I copy and paste that verbatim into a view code module? Do I need to hit a functin key to make it update? If you were able to open my XLSM and can apply it to the first few sheets (weeks 1-3 for example) so I can see it working I'd be really grateful

  8. #8
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,938
    Articles
    0
    Excel Version
    O365
    It all depends upon your exact requirement Andy.

    If you will need to do this sort of thing regularly and/or often, then you would be best to add some mechanism to launch the macro, such as an on-sheet button, or add a button to the ribbon.

    If it is just a once a year thing, I would just run that macro on the workbook, and use that as a template for each new year.

    If it is a once off just run that macro on the workbook.

    Looking at your workbook though, I am guessing it needs quite a bit more, such as building the site roster for each week, catering for a 53 week year. How does the shift pattern get allocated?
    Last edited by Bob Phillips; 2022-01-16 at 06:06 PM.

  9. #9
    Acolyte AndyDuncombe's Avatar
    Join Date
    May 2016
    Posts
    24
    Articles
    0
    Excel Version
    365
    The intention is to copy and paste the base roster into each 53 worksheets and amend manually
    But I want an automated way of naming each sheet - hence callng from a list in the shift names worksheet where I can simply amend one cell which will have a global effect

  10. #10
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,938
    Articles
    0
    Excel Version
    O365
    I understand that Andy, but it doesn't really answer my questions.

    If it were me, I would just add each new week as required, via code. I would have another routine to setup a new year. To facilitate this, I would have a default roster for each staff member, as you say, you can amend as required.

    If you can give me a few days, I can knock-up an example of what I am thinking for you to look at.

Page 1 of 3 1 2 3 LastLast

Posting Permissions

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