Rename Sheets based on List

[Keep It Simple Stupid]

I know you can rename the sheet based on whatever is in a particular cell on
that sheet, but I would like to have a "Master" sheet with a range that I
would like to rename the rest of the sheets.

(Since there are many nuances to this workbook, I really want to do it this
way). Basically, each day of the month has a different tab (each month has a
different workbook). So on this "Master Sheet" I would like to just list the
days of the month (i.e. M_11-16-09), hit the play button, and magically all
the existing sheets will be named to each one of those dates. There are
plenty of Sheets that can be renamed, I just need to figure out how to set up
the loop.

 

[Gord Dibben]

I'll give you a couple of macros for examples only.

Sub NameWS()
'name sheets with list in A1:A100 on first sheet
'will error out with dates due to illegal characters
On Error Resume Next
For i = 2 To Worksheets.Count
Sheets(i).Name = Sheets(1).Cells(i, 1).Value
Next i
End Sub

Sub NameSheets()
'Chip Pearson Feb 14th, 2007
'no need for a list on first sheet
'just enter month number in inputbox
Dim Ndx As Long
Dim StartMonth As Variant
StartMonth = Application.InputBox(prompt:="Enter the month number.",
Type:=1)
If StartMonth = False Then
Exit Sub
End If
For Ndx = 1 To ActiveWorkbook.Worksheets.Count
ActiveWorkbook.Worksheets(Ndx).Name = Format(DateSerial( _
IIf(StartMonth = 1, Year(Now) + 1, Year(Now)), StartMonth, Ndx), _
"dd mmm yyyy")
Next Ndx
End Sub


Gord Dibben MS Excel MVP

 

[Gary''s Student]

Let us assume that there are 31 daily sheets and the 32rd sheet is the
"Master":

Sub SheetNamer()
Sheets("Master").Activate
For i = 1 To 31
Sheets(i).Name = Cells(i, 1).Value
Next
End Sub


This tiny macro uses cells A1 thru A31

 

[ker_01]

I was just trying to think of some creative way to name the sheets without
relying on the Master sheet, but Excel would still need to know what month
and year it was, and then calculate dates until it hit the next month. I'm
only online for a minute, so I won't go that direction.

That made me also think that there may be an issue with months with fewer
than 31 days (multiple source cells = ""), so my most simple advice to the OP
is to populate the extra cells through cell 31 with unique values that aren't
your date strings.

Alternatively, just check each cell to verify it has a value before
processing (note that duplicate values may still cause errors because I don't
think Excel will like duplicate sheet names)

Fortunately, GS already did the hard work ;-)

Sub SheetNamer()
Sheets("Master").Activate
For i = 1 To 31
If Cells(i, 1).Value <> "" then Sheets(i).Name = Cells(i, 1).Value
Next
End Sub

or to avoid the possibility of leaving unnamed sheets in the sequence (as
unlikely as that appears to be);

Sub SheetNamer()
Sheets("Master").Activate
SheetCounter = 0
For i = 1 To 31
If Cells(i, 1).Value <> "" then
SheetCounter = SheetCounter+1
Sheets(SheetCounter).Name = Cells(i, 1).Value
End If
Next
End Sub

HTH,
Keith

 

[James Igoe]

Private Sub Update_RenameWorksheets_Sheet(ByRef wkbReport As Workbook, ByVal
strNewNames as String)

Dim strTempHolder As String
Dim strTempHolder_Initial As String
Dim varSheets As Variant
Dim intCounter As Integer

strTempHolder_Initial = strNewNames

If strTempHolder_Initial <> "NULL" Then

varSheets = Split(strTempHolder_Initial, ",")

For intCounter = 0 To UBound(varSheets)

'todo: extend to avoid conflict with names

If Len(Trim(varSheets(intCounter))) > 0 Then
wkbReport.Worksheets(intCounter + 1).Name =
Left(Trim(varSheets(intCounter)), 31)
End If

Next intCounter

End If

End Sub

 

[James Igoe]

Just to make it closer to what you need:

Create a named range to hold the sheet names with a dynamically expaning
reference:

=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A), COUNTA(Data!$1:$1))

Iterate through each cell of the named range, setting the name to the
worksheets, corresponding by number.

Make sure the number of worksheets is sufficient to cover the cell in the
range, so that you might need to add worksheets to match the number of days.