Display actual sheetname in a cell not the rename sheetname

[vandango05]

Hi all.

I wonder if someone can help me to display the actual sheetname in a cell rather than what you rename it to?

For example Sheet 195 - renamed to "Master Blank Sheet". I would like to display Sheet195 in a cell not "Master Blank Sheet"

See attached screenshot.

Thanks in advance!

 

[jasonb75]

Something like

ws1.Range("A1").Value = ws1.Codename

Where ws1 is a variable that refers to the sheet.

 

[vandango05]

Thanks for your reply, i'm afraid I don't really know what i'm doing with that.

 

[jasonb75]

That was just an example as it is not clear from your post exactly what is expected in terms of how you intend to identify which sheet to take the name from, or where the name should be entered.

There is no formula that will do what you want, it will need to be done in vba.

 

[vandango05]

Sorry I wasn't clear, I know where is no formula to display the codename only the worksheet name. On each worksheet id like to display the codename of the specific sheet in cell A1.

 

[Fluff]

How about

Sub vandango()
   Dim Ws As Worksheet
   
   For Each Ws In Worksheets
      Ws.Range("A1").Value = Ws.CodeName
   Next Ws
End Sub

 

[vandango05]

Perfect, thank you so much for your help.

 

[Fluff]

Glad we could help & thanks for the feedback.