Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Worksheets names in cells

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Hengelo
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi everybody,

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

    Martin J.A. Maatman Oonk

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =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 ]

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    New Member
    Join Date
    Feb 2002
    Location
    East Coast
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  6. #6
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •