Sub CelltoSheetName()
Application.ScreenUpdating = False
Dim i As Long
For i = 1 To Sheets.Count
Sheets(i).Name = Sheets(i).Cells(19, 6).Value
Next
Application.ScreenUpdating = True
End Sub
'
' CelltoSheetName Macro
Excel 2007 | |||
---|---|---|---|
S | |||
6 | Sheet1 | ||
Sheet1 |
Excel 2007 | |||
---|---|---|---|
S | |||
6 | Sheet2 | ||
Sheet2 |
Excel 2007 | |||
---|---|---|---|
S | |||
6 | Sheet3 | ||
Sheet3 |
Excel 2007 | |||
---|---|---|---|
S | |||
6 | pile-it Mark 1 | ||
Sheet1 |
Excel 2007 | |||
---|---|---|---|
S | |||
6 | pile-it Mark 2 | ||
Sheet2 |
Excel 2007 | |||
---|---|---|---|
S | |||
6 | pile-it Mark 3 | ||
Sheet3 |
Excel 2007 | |||
---|---|---|---|
S | |||
6 | pile-it Mark 1 | ||
pile-it Mark 1 |
Excel 2007 | |||
---|---|---|---|
S | |||
6 | pile-it Mark 2 | ||
pile-it Mark 2 |
Excel 2007 | |||
---|---|---|---|
S | |||
6 | pile-it Mark 3 | ||
pile-it Mark 3 |
Sub CelltoSheetName_V2()
' hiker95, 01/11/2017, ME982790
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
With ws
If Not .Range("S6") = vbEmpty Then
.Name = .Range("S6").Value
End If
End With
Next ws
Application.ScreenUpdating = True
End Sub
Sub CelltoSheetName_V3()
' hiker95, 01/11/2017, ME982790
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
With ws
If Not .Range("A1") = vbEmpty Then
.Name = .Range("A1").Value
End If
End With
Next ws
Application.ScreenUpdating = True
End Sub
Works GREAT! code was just what i needed! glad i was on a test book. seems S6 was not what i thought it was on my reference pages. so i moved the name cell to the header where it could be safe and consistent with formulas.
it seems the more i learn, the more i want to change, and that causes many changes, to formulas, names and formatting that had been stagnant for over a decade