Results 1 to 10 of 24

Thread: Worksheet named from cell

Hybrid View

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

    Worksheet named from cell

    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

Posting Permissions

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