Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    How many worksheets can you put in workbook? (Office 97 / xp)

    Hi

    Can anyone tell me how many worksheet can you have in workbook?

    Braddy

    Thanks
    If you are a fool at forty, you will always be a fool

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: How many worksheets can you put in workbook? (Office 97 / xp)

    According to Microsoft, the number of Worksheets in a workbook is "Limited by available memory". For more info see MSKB article 264626:

    Description of Excel 2000 specifications

    AFAIK this limitation applies to other versions of Excel as well. You can also find this info in Excel Help, search for "Microsoft Excel specifications".

    HTH

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: How many worksheets can you put in workbook? (Office 97 / xp)

    You might be able to have a lot of them, but navigation becomes an issue when you get too many of them. I inherited one that had over 300 sheets and to find the right sheet was "problem".

    We solved it by creating an "index sheet" which used hyperlinks to go to the appropriate sheet. Each worksheet had "keywords" and cross-referenced in the index so that each sheet had 3-10 different words which linked to it. I had a button on each sheet to get back to the "index".

    After creating the index, I stopped display of the tab names, to help eliminate accidentally changing a sheet name (and screwing up the index for that sheet) as there was no need to use them any more.

    Steve

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: How many worksheets can you put in workbook? (Office 97 / xp)

    Hi Mark

    Thanks to you and Steve for your replies.

    Braddy
    If you are a fool at forty, you will always be a fool

  5. #5
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How many worksheets can you put in workbook? (Office 97 / xp)

    I'd like to do this same thing but the other way. Meaning I'd like to take the sheet tab name and put it in a cell, so if the tab changes the cell data changes.
    Thanks,
    Deb

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

    Re: How many worksheets can you put in workbook? (Office 97 / xp)

    There are a couple of problems with what you are asking.

    1- Unfortunately, changing a sheet name does not trigger any event. Therefore, there would be no way to have the cell containing the sheet name updated automatically. You would have to have something else trigger updating the cell.

    2- You could write a user defined function that would return the name on the tab of a sheet. The only problem is what do you pass to the UDF to tell it which sheet you want the name of?

    If you can be more specific about how you want this to work, maybe we can come up with something. What exactly are you trying to accomplish? One thing that could be done would be to put code in a worksheet activate event that would update a list of worksheet names every time the worksheet is activated. Would that do what you need?
    Legare Coleman

  7. #7
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How many worksheets can you put in workbook? (Office 97 / xp)

    I did this about 2 years ago, with some help. I had one of my co-workers ask to have that done. I remember going out to the Knowledge Database on Mircosofts site to get the VBA code and then I received helped getting the code to work (not overly familiar with coding). But now I can't find it in the database so I figured I'd try this site. Basically all I want is whatever is on the current sheets, sheet tab, I want that value to be in cell A1. If the sheet tab changes, it updates A1.
    Thanks,
    Deb

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

    Re: How many worksheets can you put in workbook? (Office 97 / xp)

    Ok, here is something that seems to work. Put the following User Defined Function in a general module.

    <pre>Public Function MyName() As String
    Application.Volatile
    MyName = ActiveSheet.Name
    End Function
    </pre>


    Then put:

    <pre>=MYNAME()
    </pre>


    into cell A1.
    Legare Coleman

  9. #9
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How many worksheets can you put in workbook? (Office 97 / xp)

    That does it. Thanks a ton.
    Deb
    <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

  10. #10
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How many worksheets can you put in workbook? (Office 97 / xp)

    Ok, spoke too soon. I have about 30 sheets with different names. With the code you provided, all 30 sheets have the sheet name of whatever sheet is active. I want sheet 1 to put sheet 1's name in cell a1, and sheet2 to put sheet2's name in cell a1 on sheet 2 and so on. I'm assuming probably a minor code change.
    Thanks,
    Deb

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

    Re: How many worksheets can you put in workbook? (Office 97 / xp)

    Yes, that is a problem with that code. Try the code below:

    <pre>Option Explicit

    Public Function MyName() As String
    Dim v As Variant
    Application.Volatile
    If TypeName(Application.Caller) = "Range" Then
    MyName = Application.Caller.Parent.Name
    Else
    MyName = "Error"
    End If
    End Function
    </pre>

    Legare Coleman

  12. #12
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How many worksheets can you put in workbook? (Office 97 / xp)

    Thanks, that fixed it. You're the best.

Posting Permissions

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