Expanding & Collapsing Sheet Tabs

%3CLINGO-SUB%20id%3D%22lingo-sub-1620924%22%20slang%3D%22en-US%22%3EExpanding%20%26amp%3B%20Collapsing%20Sheet%20Tabs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1620924%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20file%20with%20a%20large%20number%20of%20sheets.%26nbsp%3B%20I%20would%20like%20to%20create%20a%20way%20to%20expand%20and%20collapse%20the%20tabs%20for%20ease%20of%20use.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20found%20an%20example%20code%20and%20file%20here%20that%20is%20what%20I%20would%20like%20to%20do%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.experts-exchange.com%2Farticles%2F1685%2FExcel-Using-a-Sheet-Tab-as-a-Button-for-Expanding-Collapsing-Supplementary-Sheets.html%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.experts-exchange.com%2Farticles%2F1685%2FExcel-Using-a-Sheet-Tab-as-a-Button-for-Expanding-Collapsing-Supplementary-Sheets.html%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20I%20believe%20the%20code%20is%20written%20to%20hide%20all%20tabs%20EXCEPT%20(sheet%20names).%26nbsp%3B%20I%20need%20the%20code%20to%20say%20hide%20tabs%20(sheet%20names).%26nbsp%3B%20As%20an%20example%2C%20in%20the%20sample%20file%20I%20would%20like%20the%20%22Show%20My%20Guts%22%20button%20to%20only%20expand%2Fcollapse%20%22Calcs1%22%20and%20%22Calcs%202%22%2C%20and%20leave%20the%20rest%20of%20the%20tabs%20remaining.%26nbsp%3B%20Because%20I%20have%20so%20many%20tabs%20in%20my%20actual%20file%20and%20will%20be%20adding%20more%20in%20the%20future%2C%20I%20do%20not%20want%20to%20have%20to%20add%20every%20sheet%20name%20to%20the%20code%20so%20that%20it%20won't%20get%20collapsed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20for%20any%20help%20you%20can%20offer!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1620924%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1620935%22%20slang%3D%22en-US%22%3ERe%3A%20Expanding%20%26amp%3B%20Collapsing%20Sheet%20Tabs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1620935%22%20slang%3D%22en-US%22%3ECould%20I%20be%20so%20bold%20as%20to%20ask%20for%20an%20explanation%20of%20why%20there%20are%20so%20many%20individual%20sheets%3F%3CBR%20%2F%3E%3CBR%20%2F%3EI%20know%20full%20well%20there%20are%20times%20when%20this%20kind%20of%20design%20is%20fully%20warranted%3B%20I%20also%20know%20that%20many%20times%20people%20create%20multiple%20sheets%20when%20there's%20no%20need%2C%20when%2C%20in%20fact%2C%20they're%20overlooking%20the%20amazing%20abilities%20of%20Excel%20to%20extract%20and%20summarize%20data%20from%20a%20single%20database%20on%20a%20single%20sheet%2C%20producing%20as%20needed%20summaries%20of%20histories%20or%20snapshots%20of%20data%20FROM%20that%20single%20sheet%20that%20otherwise%20would%20be%20on%20one%20of%20those%20many%20many%20sheets.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1620972%22%20slang%3D%22en-US%22%3ERe%3A%20Expanding%20%26amp%3B%20Collapsing%20Sheet%20Tabs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1620972%22%20slang%3D%22en-US%22%3E%3CP%3EIt%20is%20a%20pro%20forma%20excel%20file%2C%20so%20there%20are%20many%20different%20worksheets%20set%20up%20to%20create%20a%20financial%20model.%26nbsp%3B%20Then%20I%20have%20inputted%20monthly%20accounting%20summaries%20as%20separate%20tabs%2C%20each%20which%20is%20referenced%20throughout%20the%20various%20sheets%20per%20month%20in%20various%20formulas.%26nbsp%3B%20I%20would%20like%20to%20have%20all%20of%20these%20monthly%20accounting%20sheets%20collapsible%2C%20since%20there%20are%20so%20many%20and%20more%20for%20holding%20data.%26nbsp%3B%20I%20do%20not%20want%20to%20reference%20other%20excel%20files%20because%20links%20often%20are%20broken%20and%20I'm%20working%20off%20the%20network%20at%20home%20due%20to%20COVID.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have a file with a large number of sheets.  I would like to create a way to expand and collapse the tabs for ease of use.

 

I've found an example code and file here that is what I would like to do: https://www.experts-exchange.com/articles/1685/Excel-Using-a-Sheet-Tab-as-a-Button-for-Expanding-Col...

 

However, I believe the code is written to hide all tabs EXCEPT (sheet names).  I need the code to say hide tabs (sheet names).  As an example, in the sample file I would like the "Show My Guts" button to only expand/collapse "Calcs1" and "Calcs 2", and leave the rest of the tabs remaining.  Because I have so many tabs in my actual file and will be adding more in the future, I do not want to have to add every sheet name to the code so that it won't get collapsed.

 

Thank you very much for any help you can offer!

2 Replies
Could I be so bold as to ask for an explanation of why there are so many individual sheets?

I know full well there are times when this kind of design is fully warranted; I also know that many times people create multiple sheets when there's no need, when, in fact, they're overlooking the amazing abilities of Excel to extract and summarize data from a single database on a single sheet, producing as needed summaries of histories or snapshots of data FROM that single sheet that otherwise would be on one of those many many sheets.

It is a pro forma excel file, so there are many different worksheets set up to create a financial model.  Then I have inputted monthly accounting summaries as separate tabs, each which is referenced throughout the various sheets per month in various formulas.  I would like to have all of these monthly accounting sheets collapsible, since there are so many and more for holding data.  I do not want to reference other excel files because links often are broken and I'm working off the network at home due to COVID.