How do I get to cell to insert a tab (worksheet) name on the same.

[DC appleyards]

We have a series of sheets within one workbook. On each worksheet we want the
name of the worksheet (tab) to appear in a specific cell.

i.e E13 = the worksheet (tab) name

[Nigel]

Worksheets(1).Range("E13").Value = Worksheets(1).Name

this allows you to control a loop to name all sheets eg...

Dim xs as integer
for xs = 1 to worksheets.count
worksheets(xs).Range("E13").Value = Worksheets(xs).Name
next xs

or simply for the current sheet just use.....

Range("E13").Value = ActiveSheet.Name

--
Cheers
Nigel



"DC appleyards" <DC appleyards@discussions.microsoft.com> wrote in message
news:ABB91C45-1001-446A-A72E-401BD665B3A8@microsoft.com...
> We have a series of sheets within one workbook. On each worksheet we want
the
> name of the worksheet (tab) to appear in a specific cell.
>
> i.e E13 = the worksheet (tab) name

[Paul B]

DC, put this i E13, note workbook must have been saved for this to work
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003


"DC appleyards" <DC appleyards@discussions.microsoft.com> wrote in message
news:ABB91C45-1001-446A-A72E-401BD665B3A8@microsoft.com...
> We have a series of sheets within one workbook. On each worksheet we want
> the
> name of the worksheet (tab) to appear in a specific cell.
>
> i.e E13 = the worksheet (tab) name

[Manya S]

I used your =MID... formula and it worked great, my associate who asked me
how to achieve this will be very PLEASED. She was told that it required some
long program, but once I set up the formula I copied it to each worksheet in
a different location to test it out. So one key-in can be copied to any
worksheet. This is GREAT!!

"Paul B" wrote:

> DC, put this i E13, note workbook must have been saved for this to work
> =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
> --
> Paul B
> Always backup your data before trying something new
> Please post any response to the newsgroups so others can benefit from it
> Feedback on answers is always appreciated!
> Using Excel 2002 & 2003
>
>
> "DC appleyards" <DC appleyards@discussions.microsoft.com> wrote in message
> news:ABB91C45-1001-446A-A72E-401BD665B3A8@microsoft.com...
> > We have a series of sheets within one workbook. On each worksheet we want
> > the
> > name of the worksheet (tab) to appear in a specific cell.
> >
> > i.e E13 = the worksheet (tab) name
>
>
>

[Manya S]

Upon further experiments with this formula, I've discovered that it takes all
cell formatting set-ups with it. So format the cell the way you want it first
before copying to other worksheets. One size fits all. If you wish different
formats, I suggest creating a master workbook with worksheets for each
different format to copy to other workbooks.

"Manya S" wrote:

> I used your =MID... formula and it worked great, my associate who asked me
> how to achieve this will be very PLEASED. She was told that it required some
> long program, but once I set up the formula I copied it to each worksheet in
> a different location to test it out. So one key-in can be copied to any
> worksheet. This is GREAT!!
>
> "Paul B" wrote:
>
> > DC, put this i E13, note workbook must have been saved for this to work
> > =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
> > --
> > Paul B
> > Always backup your data before trying something new
> > Please post any response to the newsgroups so others can benefit from it
> > Feedback on answers is always appreciated!
> > Using Excel 2002 & 2003
> >
> >
> > "DC appleyards" <DC appleyards@discussions.microsoft.com> wrote in message
> > news:ABB91C45-1001-446A-A72E-401BD665B3A8@microsoft.com...
> > > We have a series of sheets within one workbook. On each worksheet we want
> > > the
> > > name of the worksheet (tab) to appear in a specific cell.
> > >
> > > i.e E13 = the worksheet (tab) name
> >
> >
> >

[Sunil Jayakumar]

You could try pasting this code in the "ThisWorkbook" section

It will automatically insert the name into the cell. If this is for
printing, I'd recommend selecting the footer/header from the print setup....

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Range("E13").Select
ActiveCell.Value = ActiveSheet.Name
End Sub


"DC appleyards" <DC appleyards@discussions.microsoft.com> wrote in message
news:ABB91C45-1001-446A-A72E-401BD665B3A8@microsoft.com...
> We have a series of sheets within one workbook. On each worksheet we want
> the
> name of the worksheet (tab) to appear in a specific cell.
>
> i.e E13 = the worksheet (tab) name

[David McRitchie]

Hi ...,
You could actually use a Worksheet Solution:

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

The CELL formulas with "filename" will not work until the file has been saved (#VALUE! error).

If you want a macro to generate the worksheet name into cell E13 of each
sheet it would be safer to generate the formula into the cell rather than using
application.activesheet.name

WARNING the following would change every sheets cell E13
You could make this safer by checking that the cell is empty first.
Public Sub Messwith_E13_LoopSheets() Application.Calculation = xlManual 'xl97 up use xlCalculationManual
Application.ScreenUpdating = False Dim csht As Long For csht = 1 To ActiveWorkbook.Sheets.Count 'worksheet or sheets
Sheets(csht).Range("E13").Formula = _ "=MID(CELL(""filename"",A1),FIND(""]"",CELL(""filename"",A1))+1,255)" Next csht
Application.ScreenUpdating = True Application.Calculation = xlAutomatic 'xl97 up use xlCalculationAutomaticEnd SubMore
information in the following web pages:
http://www.mvps.org/dmcritchie/excel/pathname.htm
http://www.mvps.org/dmcritchie/excel/sheets.htm

Please use your name in the newsgroups, at least in your signature if not in your
email address.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"DC appleyards" <DC appleyards@discussions.microsoft.com> wrote in message
news:ABB91C45-1001-446A-A72E-401BD665B3A8@microsoft.com...
> We have a series of sheets within one workbook. On each worksheet we want the
> name of the worksheet (tab) to appear in a specific cell.
>
> i.e E13 = the worksheet (tab) name