Display actual sheetname in a cell not the rename sheetname

vandango05

Board Regular
Joined
Oct 6, 2008
Messages
109
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!
 

Attachments

  • screenshot.jpg
    screenshot.jpg
    12.8 KB · Views: 0

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,419
Office Version
  1. 365
Platform
  1. Windows
Something like
VBA Code:
ws1.Range("A1").Value = ws1.Codename
Where ws1 is a variable that refers to the sheet.
 

vandango05

Board Regular
Joined
Oct 6, 2008
Messages
109
Thanks for your reply, i'm afraid I don't really know what i'm doing with that.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,419
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Oct 6, 2008
Messages
109

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub vandango()
   Dim Ws As Worksheet
   
   For Each Ws In Worksheets
      Ws.Range("A1").Value = Ws.CodeName
   Next Ws
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,205
Messages
5,623,364
Members
415,969
Latest member
Rey99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top