Vba code to sheet name equal cell name

[Lacan]

Simple Question:

Have in cell B2 the name of my customer that would like that sheet name return the same equal name in B2.

How to do so?

Thanks for the great advice.

 

[pile-it Mark]

i would like this to apply to all sheets on an individual basis (S6) is different on every sheet of the workbook. so every tab is named different.

but i get
Run-time error '1004
Application-defined or object-defined error

Sheets(i).Name = Sheets(i).Cells(19, 6).Value

i was not sure if S6 is (19, 6)

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

 

[hiker95]

pile-it Mark,

Here is a new macro solution for you to consider.

Sample worksheets before the macro:

Excel 2007
	S
6	Sheet1
Sheet1

Excel 2007
	S
6	Sheet2
Sheet2

Excel 2007
	S
6	Sheet3
Sheet3

And, if we change each of the cells S6 to a different name:

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

And, run the macro again:

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

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

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

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the CelltoSheetName_V2 macro.

 

[pile-it Mark]

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.

Thank you for all your help!

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

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

 

[hiker95]

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.

pile-it Mark,

Thanks for the feedback.

You are very welcome. Glad you were able to update the macro.

 

[hiker95]

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

pile-it Mark,

See if something, in the below link(s), of my most up to date list will help you:

Training / Books / Sites as of 01/03/2017

http://www.mrexcel.com/forum/excel-...rn-visual-basic-applications.html#post4723068