working with MANY sheets in a single excel file

[sami770]

Guys,
I am working with a single excel file that has about 70 different sheets.
Its difficult (and takes time) to manually navigate to each sheet, it would be great if I could order the sheets into few categories, maybe something similar to the way we bookmark sites in the web browser, so if you have many sites you can categories them into groups.
Any Ideas?

(I have excel 2010)

Sami

[Blake 7]

to group worksheets press and hold down ctrl and select the sheets you want.

You could always use this macro to group sheets by tab colour so as to hide/unhide all (in this instance) red sheets at the same time. Guess you could create the same macro for blue, breen ect.

This is from RoyUK's site.

Please Login or Register  to view this content.

[JBeaucaire]

That's a cool trick with the colored tabs. Very flexible.

When I build workbooks this large, I like to create a fast navigation system. I put a "table of contents" sheet at the beginning of the workbook. On each sheet is a Hyperlink in cell A1 that takes you driectly back to that front sheet.

On that front sheet is a hyperlink to each sheet. This way you can quickly get from any one sheet to another in two quick clicks.

Here's a macro on my site for giving you all those front sheet hyperlinks:

Create Hyperlinked List of Sheets

Once the hyperlinks are created for you, you could reorganize them on that front sheet in any visual way you wish to make them easy to see all at once.

[Blake 7]

Hi J - great site btw. I thought that I would "borrow" your code for future projects! naturally I wanted to test and learn how you did it before storing it in my bage of tricks!

Please Login or Register  to view this content.

However, when I click on a link I get an error msg .

This operation has been cancelled due to restrictions in effect ion this computer.

What did i do wrong? any clues. All i did was insert the code into a new module!!

Sorry to bother you.

Thanks

[JBeaucaire]

I'm not bothered at all, that's what the code site is for and the forum.

Also, I've never seen that message before. You might need to Google up some info on that.

Do you get the same error when you open my sample file and run it?

[Blake 7]

Hi - yes the same error from your sample. see above. I have now attached a file. Do you get the same error?

I'll start googling straight away and let you know the outcome.

Cheers J

[JBeaucaire]

No, of course not. The error you're getting is not from Excel or the file itself, I imagine it's coming from the OS. I Googled that message and found a LOT of feedback. Looks like you're in for a bit of troubleshooting.

[Blake 7]

Found the fix -

I dl'd this file

HTM/HTML Associations (Restore the default associations for htm/html files)

from this site

http://www.dougknox.com/xp/file_assoc.htm

now working perfectly.

Hope all well accross the pond. Have a good day.

[JBeaucaire]

Awesome. If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

[Blake 7]

Hi - JB. hope that you are well. If it was my thread i'd certainly mark it solved!

[royUK]

You could use a prefix on the sheet name for each group - like A_Sheet1, B_Sheet2.. Then create a UserForm that loads the sheet names based on the prefix.

Here's a quick example