Worksheet named from cell

[AndyDuncombe]

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!

[Bob Phillips]

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?

[AndyDuncombe]

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

[AndyDuncombe]

HI Bob Phillips;47096 - any luck with this? Feel free to drop me a line on andyduncombe@aol.com

[NoS]

This in a standard module should work

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 ???

[Bob Phillips]

Sorry about the tardiness Andy, but try this

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

[AndyDuncombe]

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

[Bob Phillips]

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?

[AndyDuncombe]

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

[Bob Phillips]

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.