Inserting Shet Tab name into Cell...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I know it can be done, but I just don't have the patience to finagle with all the different codes. I want to insert the name of each sheet into a particular cell in Excel. For instance, Sheet 1 is named Math, Sheet 2 is named History, Sheet 3 is named Biology, and so forth. How do I get the name of each sheet into cell G4 -- on each sheet. Tried to do a backward process of elimination with that "filename" command, but didn't quite work it out. Thanks for any help!
 
On each sheet put this formula

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


--

Regards,

Peo Sjoblom


Cheryl said:
Hi,
I know it can be done, but I just don't have the patience to finagle with
Click to expand...
all the different codes. I want to insert the name of each sheet into a
particular cell in Excel. For instance, Sheet 1 is named Math, Sheet 2 is
named History, Sheet 3 is named Biology, and so forth. How do I get the
name of each sheet into cell G4 -- on each sheet. Tried to do a backward
process of elimination with that "filename" command, but didn't quite work
it out. Thanks for any help!
 
Cheryl said:
Hi,
I know it can be done, but I just don't have the patience to finagle with
Click to expand...
all the different codes. I want to insert the name of each sheet into a
particular cell in Excel. For instance, Sheet 1 is named Math, Sheet 2 is
named History, Sheet 3 is named Biology, and so forth. How do I get the
name of each sheet into cell G4 -- on each sheet. Tried to do a backward
process of elimination with that "filename" command, but didn't quite work
it out. Thanks for any help!

Use this formula in G4 on each sheet:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
Don't edit this at all - "filename" does NOT mean you have to put the
filename in here!
The workbook must be saved for this to work.
 
Found it...after doing more extensive searching on this site...I found it from at least 2 people, Max was one, not sure of who the other person was.

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

Also, I noticed that, since I want this info to go into the same cell on each worksheet (I have 8 in this particular workbook) one can do a copy/paste of this formula: copy the formula; in Excel, select all the worksheets (tabs) by right-clicking on one tab & click on Select All Sheets, go to the cell in question such as G4, and paste, using Ctrl V or whatever you use to paste. Each sheet tab is now in that particular cell on each sheet...thing to be mindful of is to UNGROUP those sheets when you're done, otherwise whatever you type or format on one sheet is done automatically on the others. Thanks!! Cheryl
 
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))
+1,255).Will put sheet name in cell

Private Sub Worksheet_Activate()
ActiveSheet.Name = Range("A1")
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
ActiveSheet.Name = Range("A1")

End Sub

Will put name on sheet from cell if entered as code. Right
click sheet view code.

Pul
-----Original Message-----
Hi,
I know it can be done, but I just don't have the patience
Click to expand...
to finagle with all the different codes. I want to insert
the name of each sheet into a particular cell in Excel.
For instance, Sheet 1 is named Math, Sheet 2 is named
History, Sheet 3 is named Biology, and so forth. How do I
get the name of each sheet into cell G4 -- on each sheet.
Tried to do a backward process of elimination with
that "filename" command, but didn't quite work it out.
Thanks for any help!
 
Back
Top