Cascading excel window tabs - Can you??

[Shelly68]

My spreadsheet has approx 50 tabs, can I cascade them so that I don't have to scroll across all the time to find the one I want (ie, can they all be shown in the one window)

TIA

Shelly

 

[venkat1926]

you can cascade.in blank space in the statusbar rightclik and choose how you want to tile the windows.

another method is
there are arrows to the left of the first tab at the bottom. rightlcik you get the names of all the sheets and you click the relevant sheet

if you are particular about any particualr sheet yu can place a hyprlink in all the other sheets.

see excel help under keyword "worksheet"

 

[Shelly68]

This does not work,

I am talking about one file only, I understand how to cascade on multiple files, but this is only for one file with 50 different spreadsheets contained within.

50 tabs, can I cascade them into rows of say 10 (ie, 5 rows of ten)

 

[ADAMC]

Hi Shelley not sure if i understand you right but if i know what you mean it can be a pain

All i can think of is creating a summary page with hyperlinks to all the other tabs:

Sub HyperLink_SheetsToIndex()
     
     '   Local  Variables
    Dim wks                 As Worksheet
    Dim rngLinkCell         As Range
    Dim strSubAddress       As String, strDisplayText       As String
     
     '    Step 1 :  Loop through all worksheets
     ' 1a : Clear all current  hyperlinks
    Worksheets("Sheet Index").Range("A:A").ClearContents
     ' 1b : Create Linked index list
    For Each wks In ActiveWorkbook.Worksheets
        Set rngLinkCell = Worksheets("Sheet Index").Range("A65536").End(xlUp)
        If rngLinkCell <> "" Then Set rngLinkCell = rngLinkCell.Offset(1, 0)
        strSubAddress = "'" & wks.Name & "'!A1"
        strDisplayText = "HyperLink : " & wks.Name
        Worksheets("Sheet Index").Hyperlinks.Add Anchor:=rngLinkCell, Address:="", SubAddress:=strSubAddress, TextToDisplay:=strDisplayText
    Next wks
     
End Sub

create a new sheet called Sheet Index
paste the code into the this workbook module and then run it

it will create a hyperlink on 1 sheet to every other sheet in the workbook

you can then just click the 1 you want rather than scrolling through....Perhaps!?

 

[indiantrix]

ADVERTISEMENT

Can this help your needs?
Start with, say, a 50 tab sheet, with first tab selected
Click Window | New Window
Then select the second tab in new window
Then click Window | Arrange | Cascade.
This is repeatable and tedious, but may be what you are looking for?
Hope so... /s/ Larry

 

[Shelly68]

I did the VBA code Adam, and it works, but I still need to then click back on the front page to access another one (so it's not really saving me any time), thanks anyway

Larry, I can't get your suggestion to work,

never mind, I'll just put up with it, thanks to all who tried helping, much appreciated.

 

[ADAMC]

ADVERTISEMENT

Sorry i couldnt help...yes i forgot you would have to click back to the master sheet, only thing i can thing of is to put a button on each of the worksheets which can link you back the the sheet index.

Put this code into a command button from the forms menu, you would have to then copy the button and place it on each worksheet, but if its a workbook you use all the time its a worthwhile exercise.

Sheets("Sheet Index").activate

Hope this helps.

PS indiantrix methd should do the trick also but you would have to repeat every time you opened the workbook.

One option would be some code that says:

for every worksheet in the workbook, click window> new window then when there are no worksheets left click window> arrange> cascade......

So sheet 1 activate window> new window
sheet 2 activate> window > new window etc until done on all workbooks then cascade....

I think this would work but ill play around with the code and get back to you, someone else might well be able to do this without playing around.
You could have the code in the workbook open event so it performs every time you open the workbook....Bare with my while i figure the code as its not my strong point!

 

[njimack]

A couple of options...

Assign the following code to a button on your toolbar

Sub SheetPopup()
 Application.CommandBars("Workbook Tabs").ShowPopup
End Sub

Or take a look at http://www.codematic.net/files/zips/QuickNav15.zip

It's an add-in that places a combobox on your toolbar, which lists all sheets in a workbook (it also has a combobox which lists all named ranges)

 

[tusharm]

Navigating through 50 sheets would be tedious. Something like the below may help or maybe nothing can help with so many sheets
Workbook Navigator for Excel
http://www.tushar-mehta.com/excel/software/utilities/wb_nav.html

My spreadsheet has approx 50 tabs, can I cascade them so that I don't have to scroll across all the time to find the one I want (ie, can they all be shown in the one window)

TIA

Shelly