Results 1 to 5 of 5
  1. #1
    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: Excel 2003 (I don't know)

    1) Chip Pearson has some code for Sorting Worksheets In A Workbook

    2) I don't understand your question 2, could you elaborate?

    I am making a leap here, so I apologize if I am wrong about your setup (I admit to not understanding all the goals and setup):
    If you are looking for creatiing 100 "identically formatted" sheets to hold data, there is much better ways of storing the data which allow analysis, outputs, etc using data's builtin capabilities.

    It would be better to 1 "master sheet" with all the data and you could have an extra column to differentiate what you doing now by "sheet". The data should be setup in a table (rows/columns format.

    if you want an output a particular way (or ways) you could setup sheets to extract from this datatable into the desired form

    This setup would allow using filtering, pivot tables, etc to summarize analyze, extract the data, etc.

    If you want more info could you explain what you have and what you want to be able to do with it and we can advise you further and provide more details.

    Steve

  2. #2
    Star Lounger
    Join Date
    Mar 2002
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating and sorting worksheets (Excel 2003)

    Subject edited by HansV to be more informative than "Excel (I don't know)"

    Greetings -
    I want to create a workbook with approx. 100 worksheets for tracking billables.

    I know how to insert the sheets but....
    1) I don't know how to arrange them alphabetically instead of the default numeric list.
    2) I don't know how to create my page form and have it repeat on all 100 sheets...

    Thanks so much for your help!

    Jay

    I also n

  3. #3
    Star Lounger
    Join Date
    Mar 2002
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2003 (I don't know)

    Thank you so much for such a speedy response. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    You did interpret my problem 2) correctly and I can see that is the best approach but I am providing this for someone with less exposure to Excel than I have and we'll have to wait to set that up.

    Question: would you be kind enough to detail the steps I take to enter Chip Pearson's code for sorting worksheets? <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    Thank you again.

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

    Re: Excel 2003 (I don't know)

    If you want to be able to use the code for sorting worksheets in every workbook, you should put it in your personal macro workbook Personal.xls. If you don't know what that is, first read Legare Coleman's <!post=Personal.xls Tutorial (All),118382>Personal.xls Tutorial (All)<!/post>. It explains how to create and use Personal.xls.

    To copy Chip Pearson's code into Personal.xls:
    - Go to the web page Steve provided a link to.
    - Select the dark blue code from Sub SortWorksheets() up to and including End Sub.
    - Press Ctrl+C or select Edit | Copy to copy the text to the Windows clipboard.
    - Switch to Excel.
    - Activate the Visual Basic Editor (Alt+F11)
    - Click on Personal.xls in the Project Explorer on the left hand side.
    - Either open an existing module in Personal.xls, or create a new one (Insert | Module).
    - Press Ctrl+V or select Edit | Paste to paste the code into the module window.
    - Switch back to Excel (Alt+F11)

    To run the macro on a workbook in which you want to sort the worksheets:
    - If you want to sort specific sheets only, select them, otherwise make sure that only one sheet tab is selected.
    - Select Tools | Macro | Macros...,
    - Locate and select SortWorksheets in the list of available macros.
    - Click Run.

  5. #5
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2003 (I don't know)

    You can create 100 identical sheets in a few ways:
    1. with the original 3 default sheets (on new open workbook), select all 3 tabs (by using Ctrl key when you select the tabs) and then right-click the grouped tabs (they'll be white), select copy. You have to repeat this many times for 100 sheets total but each time you can group more and more sheets. The downside of this is that the sheet names will be very difficult to work with (something like Sheet1(copy1),etc. which you'll later want to rename to something more readable. At that time, you'll again have another problem of manually renaming all 100 sheets! I think it's better to do this create/name sheets in code.

    2. Once you have 100 sheets created (and named properly), you can select all 100 sheets (which won't be easy to do w/o code), and then select any of these sheets as a starting point to write your data, formulas, formatting, etc. This will cause all the selected sheets to receive the same data, formulas, formatting as your 'source' sheet.

    After saying all this, it's really a very bad idea to have 100 virtually identical sheets. Think of it from the user's point of view - how are they going to move from sheet3 to sheet98, for example? You won't be able to see all 100 tabs on the workbook AND you can't even easily view all 100 sheet names with the quick/dirty sheet view button (on bottom left of workbook that looks like a VCR control).

    As others have said, it's much better to have one (or a few) master sheets where all the duplicate data is stored (and these sheets can be hidden so they're protected) and use other sheets for any reports you might want the user to see.

    Deb

Posting Permissions

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