naming tabs from cells on diff sheet

  • Thread starter Thread starter duckie
  • Start date Start date
D

duckie

I have 71 worksheets, on sheet 1 in column B from B4 there are 70
names in cells, how do I get the name onto the tabs for the next 70
sheets. Plus on each sheet in D1 the name appear as well,
I may have to make 2 workbooks instead of having 71 worksheets in 1
workbook to make it easier. I have posted a similar request before but
I still have trouble with it. Could someone please help
 
This little macro assumes that the first sheet is named "directory", adjust
to suite:

Sub name_um()
Dim shnames(100) As String
Sheets("directory").Activate
For i = 0 To 69
shnames(i) = Cells(i + 4, "B").Value
Next
For i = 2 To 71
Worksheets(i).Name = shnames(i - 2)
Next
End Sub
 
I'm not sure what you meant by Sheet 1. Here are two example that should
help. One case Sheet1 is the index to the sheet and the other is the string
name of the sheet. You need to have the code skip the first sheet.

For Each sht In ThisWorkbook.Sheets

If sht.Index <> 1 Then
sht.Name = sht.Range("D1")
End If
Next sht
End Sub

For Each sht In ThisWorkbook.Sheets

If sht.Name <> "Sheet1" Then
sht.Name = sht.Range("D1")
End If
Next sht
End Sub
 
Back
Top