Worksheets names in cells

[MJA]

Hi everybody,

I want my worksheets names to appear in
cells A1, A2 ....
Can I do this with a formula instead of VB.

[Mudface]

In VBA, this code will work: -

Public Sub SheetCount()

Dim ws As Worksheet
x = 1
For Each ws In Worksheets
Worksheets(1).Range("A" & x).Value = ws.Name
x = x + 1
Next ws

End Sub

There is a worksheet function to do this, I think Aladin Akyurek posted an answer to a similar question once, but I can't for the life of me remember what it was.

[NateO]

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

Will get you the name of the active sheet. Anyone help offsetting the sheets (obviously placing "worksheet name!" infront of a1 will work, but it's manual)?

Could put this on each sheet and do some if statements on your main page.

Incidentally, the file needs to be saved for this to work.

Cheers,

Nate

[ This Message was edited by: NateO on 2002-02-22 16:19 ]

[NateO]

Well, here's my offset. Bury this in your visual basic editor (you won't have to touch it after that):

Function SHEETOFFSET(offset)
Application.Volatile
SHEETOFFSET = Sheets(Application.Caller.Parent.Index _
+ offset).Name
End Function

Now, if your first sheet is going in row 1 then down, use the following formula:

=IF(ISERR(sheetoffset(ROW()-1)=TRUE),"",sheetoffset(ROW()-1))

If it's in row 2:

=IF(ISERR(sheetoffset(ROW()-2)=TRUE),"",sheetoffset(ROW()-2))

etc......It will show "" if there's no more sheets to show.

Incidentally, if you want to go across instead of down switch:

row with column

Have a great weekend. Cheers,

Nate

[ This Message was edited by: NateO on 2002-02-22 20:12 ]

[believer]

Seems awfully complicated to me. Assuming I'm understanding your query correctly, simply code: =CELL("filename",A1). HTH.

[NateO]

My responses do not include filepath & filename. It depends on what you want...

My second response shows how to get all of the sheets on one sheet, from a variable perspective, versus just the sheet you're on/in.

Like most things in life, it probably is more complicated than it ought to be.

fyi: The iserr kills the $&#* you get if you copy the formula 1000 rows deep and you only have 4 worksheets, etc....

Cheers, Nate

[ This Message was edited by: NateO on 2002-02-22 19:52 ]

[Chris Davison]

On 2002-02-22 11:47, NateO wrote:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,
LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

Good one Nate, I was after this the other day but couldn't find it

[NateO]

Chris, it's a good one...used this one the other week.

How's the book coming along? I was surprised to see Jim ripping up xl 2002 because he didn't like the fact the you can't put the filepathname in the footer automatically. A surprisingly easy task in vba. He didn't mention the ability to use the "speak" command which looks pretty neato.

Have a good one. Cheers, Nate

[ This Message was edited by: NateO on 2002-02-23 13:24 ]