Multiple Worksheet Cell Rename

[hyewiz]

What I need is in a workbook with 135 worksheets each has custom name, I want to enter same worksheet name in cell C1,

for example "C1 of worksheet Alex to have Alex typed" is there a way in VBA to do at once.

 

[Dim Me as xlNoob]

Give this a try:

Sub SheetNames()
    Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Worksheets
        ws.Name = ws.Range("C1").Value
    Next ws
End Sub

 

[hiker95]

I want to enter same worksheet name in cell C1

hyewiz,

If I understand you correctly, then, here is a macro solution for you to consider.

Sub SheetNameIntoCell_C1()
' hiker95, 11/16/2017, ME1031835
Application.ScreenUpdating = False
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
  ws.Range("C1").Value = ws.Name
Next ws
Application.ScreenUpdating = True
End Sub

 

[DHayes]

Hi hyewiz,
There is another way to do this without VBA to insert the name of your sheets.
1. Right click the tab and Select all sheets.
2. Insert the below formula into C1

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

All your sheets will have their independent sheet names.

 

[hyewiz]

Thank you all both formula's worked perfectly

Thank you

 

[hiker95]

hyewiz,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.