How to Organize 40+ Worsheets?

P

phil6666

Hi-

I'm using Excel 2002 and I've got a very large workbook with more than
40 worksheets.

Is there any way to organize these worksheets into sub-folders or some
other method to make it less unwieldy?

I posted this last month and rec'd the following suggestion that I
write a Table of Contents macro, but I'm afraid that's well beyond my
ability. Any other suggestions?

thnx
 
L

Lady Layla

Only other way is to move the sheets to a new file. Within a single file you
can not do what you are asking about


:
: Hi-
:
: I'm using Excel 2002 and I've got a very large workbook with more than
: 40 worksheets.
:
: Is there any way to organize these worksheets into sub-folders or some
: other method to make it less unwieldy?
:
: I posted this last month and rec'd the following suggestion that I
: write a Table of Contents macro, but I'm afraid that's well beyond my
: ability. Any other suggestions?
:
: thnx
:
:
:
: >Hi
: >this is not possible. But you may create something liek a table of
: >contents. See:
: >http://www.mvps.org/dmcritchie/excel/buildtoc.htm
: >
: >--
: >Regards
: >Frank Kabel
: >Frankfurt, Germany
:
 
P

phil6666

I went to that webpage earlier and found about 1,000 lines of code
like:
Sub BuildTOC()
'listed from active cell down 7-cols -- DMcRitchie 1999-08-14 2000-09-05
Dim iSheet As Integer, iBefore As Integer
Dim sSheetName As String, sActiveCell As String
Dim cRow As Long, cCol As Long, cSht As Integer
Dim lastcell.........
Click to expand...
etc
etc

I've done lots of EXCEL, but this looks like my old FORTRAN IV days
bake in college.

Is there a simple place for me to begin to get a clue what this is
about?

PS: I'm just playing around with a very large workbook. Finding the
42nd worksheet out of 50 is a pain! All I really need is a easy way to
jump to 4 or 5 key worksheets just to eliminate all of the lateral
scrolling....

thnx
 
M

Michael R Middleton

phil6666 -
I'm just playing around with a very large workbook. Finding the 42nd
Click to expand...
worksheet out of 50 is a pain! All I really need is a easy way to jump to 4
or 5 key worksheets just to eliminate all of the lateral scrolling.... <

Have you tried right-clicking the worksheet scroll arrows in the lower left
corner of Excel's display? That'll bring up a list of the worksheets, and it
might be easier to select the sheet you want. Perhaps.

- Mike

www.mikemiddleton.com
 
P

phil6666

That's SUPER and helps a lot!!!

I don't want to get greedy, but is there a way to show more than the
first 15 sheets when you right click like that?

thnx
 
R

RT

Hi
I've just finished solving a similar problem for my boss
Up to 10 or 15 sheets the right click was fine for him but he had some files
with up to 65 sheets!
What I did is on each sheet create an hyperlink to a cover page which acts
like a menu
A description of each sheet hyperlinked to the desired sheet
And Oh No it is not a two minute job!
But the return to menu hyperlink can be copied and pasted

It does the job for him
Good luck

Rex
 
L

Laurence Lombard

I find that if one ends up with many sheets with similar data, then the data
should rather be grouped together eg If there are sheets for Jan, Feb etc
then they could be grouped in one sheet with an identifier (Jan, Feb etc) in
the first column. Then you use filters (autofilter or similar) to see what
you want to see. This opens up many advantages, such as getting summary data
using Pivot Tables, simplicity etc.
My cents worth
Laurence Lombard
 
Top