working with MANY sheets in a single excel file

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.

Sub QuickHideUnhide()
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        If ws.Tab.Color = vbRed Then ws.Visible = Not ws.Visible
    Next ws
End Sub

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.

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!

Option Explicit

Sub CreateHyperlinkedSheetList()
'Author:    Jerry Beaucaire
'Date:      1/3/2011

Dim ws As Worksheet
Application.ScreenUpdating = False

For Each ws In ActiveWorkbook.Worksheets
    With ActiveSheet.Range("A" & Rows.Count).End(xlUp)
        .Offset(1).Value = ws.Name
        ActiveSheet.Hyperlinks.Add Anchor:=.Offset(1), Address:="", SubAddress:= _
            "'" & ws.Name & "'!A1", TextToDisplay:=ws.Name
    End With
Next ws
    
Application.ScreenUpdating = True
End Sub

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

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?

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

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.

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.

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.

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

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