Results 1 to 11 of 11
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    List Worksheet names (2000)

    This routine lists the worksheet names based on the active cell. What do I need to modify to make it list the worksheets in a specific worksheet (called "data"), starting in A1 cell?

    Sub SheetNamesDownRows()
    Dim iSheet As Integer
    For iSheet = 1 To ActiveWorkbook.Worksheets.Count
    ActiveCell.Offset(iSheet - 1, 0) = Worksheets(iSheet).Name
    Next iSheet
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: List Worksheet names (2000)

    Replace ActiveCell with Worksheets("Data").Range("A1")

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: List Worksheet names (2000)

    Thanks, again, Hans...
    One more thing, if you would...
    If, rather than the active workbook (as in: For iSheet = 1 To ActiveWorkbook.Worksheets.Count ), suppose I want to list the sheets from another workbook?!
    I couldn't find a reference to workbooks in the VBA help that would allow me to look in another workbook rather than the active.

    TIA...

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Worksheet names (2000)

    Workbooks("YourWorkBookName.xls").Worksheets.Count
    Legare Coleman

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: List Worksheet names (2000)

    I found that one on my own and tried it, but it didn't work. My Excel file name has three words in the name with a single space between the words "Client Services Form" -- is there some special treatment I need to consider because of the spaces? I've tried it with and without the spaces.

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: List Worksheet names (2000)

    I found MY problem...needed to reference that workbook elsewhere in the code also ... thanks for your help. Got it now. Phew!

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Worksheet names (2000)

    Just put it in double quotes.
    Legare Coleman

  8. #8
    3 Star Lounger baumgrenze's Avatar
    Join Date
    Feb 2001
    Location
    California, USA
    Posts
    262
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: List Worksheet names (2000)

    Why, in Redmond's name, isn't there a function in THE PROGRAM that allows a simple display of all of the worksheets in a workbook. If there is, it is not easy to find in Excel help for Excel 2000.

    Ideally, it would appear in a dropdown that you could copy and paste someplace else.

    It is a PITA for the casual user to have to resort to VBA to get something like this, or, alternatively pasting screenshots into Word and then reading off and copying the workbook names.

    Baumgrenze
    Baumgrenze
    Hier sind wir tief eingewurzelt.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: List Worksheet names (2000)

    It would be impossible for Microsoft to fulfill every wish of every user. The option to list the names of worksheets can be handy, but we don't get a lot of questions about it here, so ...

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: List Worksheet names (2000)

    Not quite what you are looking for, but if you right-click on the "VCR buttons" on the bottom, left of the sheet name tabs, you get a popup list of up to 15 sheetnames, after 15 you see the first 15 and you can select "More sheets ..." and see them all in a scrollable window. But you can't copy from there. <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15> (I think it was <!profile=Rory>Rory<!/profile> or Jan Karel <!profile=pieterse>pieterse<!/profile> who taught me that?)
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    3 Star Lounger baumgrenze's Avatar
    Join Date
    Feb 2001
    Location
    California, USA
    Posts
    262
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: List Worksheet names (2000)

    Thank you John,

    You know, I thought I remembered being taught something like that in 1997, when I was allowed to take a bunch of computer courses at company expense just before they gave me a brass parachute. That's why I looked in the Excel Hellp file. Perhaps if I wasn't so hard headed, Clippy would have been glad to help me remember just how to do it. I'm glad there are alternative ways, like this forum!!

    I found another workaround. I am a strong advocate of ZTree (heir to the XTree mantle) which has a 'gather' function while a file is being displayed. Near the top of the display is a listing of the worksheets, separated by bits of code (mostly
    Baumgrenze
    Hier sind wir tief eingewurzelt.

Posting Permissions

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