Sheet Formula 1

[robcarr]

Dear All,

Does anybody know the formula to get the sheet name, I have lots of sheets with nearly identical data, how can I quickly put the sheet name in A1, so when I print I can tell the sheet name easily.

thanks in advance

 

[JustinEzequiel]

Dim oWS As Worksheet

For Each oWS In ActiveWorkbook.Sheets
  oWS.Range("A1").Value = oWS.Name
Next oWS

 

[Loomah]

Or...
You could add the sheetname as a header/footer

;-) If a man says something and there are no women there to hear him, is he still wrong?
"...Three Lions On A Shirt..."

 

[DaleWatson123321]

Here's a formula option...

There are probably other options, but these formulas will work.  The following provides you with two options:
1) without the ".xls" suffix, and 2) with the suffix.
enter in A3: =CELL("filename",A2)
enter in A4: =FIND(".xls",A3,1)
enter in A5: =FIND("[",A3,1)+1
enter in A6: =MID(A3,A5,(A4-A5))
enter in A7: =MID(A3,A5,(A4-A5)+4)

Hope this is what you wanted.

Regards,    ...Dale Watson dwatson@bsi.gov.mb.ca
    

 

[xlbo]

Or all in one go
=MID(CELL("FILENAME",F10),FIND("]",CELL("FILENAME",F10))+1,255)

NB - workbook needs to be saved before this will work

HTH
Geoff

 

[DaleWatson123321]

Hi Geoff,

I just think it's just GREAT that we all "chip in" with our various alternative solutions. It's obviously what makes Tek-Tips "that much better".

But can you please double-check your formula ? I did, and it gives me the "SHEET" name.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

Whoops

...not much sleep last night.

I JUST realized it IS the SHEET name that is being asked for !!!

...back to sleep

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

P.S. ...and a STAR for you, Geoff.

I consider that you "won fair and square".

 

[xlbo]

Thanks Dale - and as a piece de resistance, here's 3 other VERY useful little functions
NB - I didn't work 'em out - that credit must go to Mr Julian Milano
To display the full title of the workbook in a cell:
=CELL("FILENAME",F10)
eg. H:\MSOffice\Macros\[Accessing document properties.xls]Sheet1

To display the Path:
=MID(CELL("FILENAME",F8), 1,FIND("[",CELL("FILENAME",F8))-1)
eg. H:\MSOffice\Macros
To display the filename:
=MID(CELL("FILENAME",F9),FIND("[",CELL("FILENAME",F9))+1,FIND("]",CELL("FILE
NAME",F9))-FIND("[",CELL("FILENAME",F9))-1)
eg. Accessing document properties.xls

To display the Sheet name:
=MID(CELL("FILENAME",F10),FIND("]",CELL("FILENAME",F10))+1,255)
eg. Sheet1

All of these will only work once the workbook is saved however

Rgds
Geoff