Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: copyable list of worksheet names

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'd like to copy a list of the names of all the worksheets in a workbook. File Preferences shows a list of worksheet names but it's view-only text--I can't copy it. Is there a way to get a list I can copy?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ASAP Utilities, a popular freeware add-in, has an "Information | List all sheet names" menu command. See www.asap-utilities.com. There's hardly a day that goes by that I don't use one of its features!

    [ This Message was edited by: Mark W. on 2002-04-26 15:28 ]

  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

    I made use of a function to do this a while back. If you put the following in a standard module:

    Code:
    Function SHEETOFFSET(offset)
    Application.Volatile
    SHEETOFFSET = Sheets(Application.Caller.Parent.Index _
    + offset).Name
    End Function
    Now, on the first sheet, if your first sheet is going in row 1 then down, use the following formula:

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

    and copy down

    If it's in row 2:

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

    and copy down

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

    Incidentally, if you want to copy accross instead of down switch "row" with "column" in the worksheet formulas.

    A different way to pluck a duck.

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-04-26 15:57 ]

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How about a quick VBA solution? Change the sheet referencing to suit.

    Sub test()
    Dim Counter As Integer, ws As Worksheet
    Counter = 1

    For Each ws In ThisWorkbook.Worksheets
    Sheet1.Cells(Counter, 1) = ws.Name
    Counter = Counter + 1
    Next ws
    End Sub

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
  •