showing all tabs at bottom of page

D

derwood

i have a workbook with over 100 sheets . is there a way for these to be
tiled together vertically as opposed to horizontally across the page
and out of sight
 
R

Ron de Bruin

Hi derwood

No, but you can right click on the arrows next to your first sheet tab.
Maybe you like that
 
J

Jim May

Hi derwood:

Hear's a technique I've used (where 100+ sheets are involved in a Workbook.
Create a New Sheet in the First Position (Far left - 1st in book as you see
them)
Name it "Index" (without the quotes).

Paste this code into a standard Module:

Sub ListWSNames()
Dim Ws As Integer
Dim iCol As Long, iRow As Long
With Worksheets("Index")
.Cells.ClearContents
iCol = 2: iRow = 1
For Ws = 2 To Worksheets.Count
.Cells(iRow, iCol) = Worksheets(Ws).Name
.Cells(iRow, iCol - 1) = Ws - 1
iRow = iRow + 1
If iRow Mod 21 = 0 Then
iCol = iCol + 2
iRow = 1
End If
Next
End With
Range("B1").Select
ActiveSheet.Range("A:N").Columns.AutoFit
End Sub

Then also while in the VBE environment double-click on the sheet "Index"
(you just created) and paste in this code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim Ws As Worksheet
Dim WsName As String
WsName = Target.Value
Sheets(WsName).Activate
Cancel = True
End Sub

Now your set
First thing to do is Run Macro ListWSNames
From your spreadshhet, at the menu, click on Tools,
Macro, Macros... - Select LiatWSNames Click the Run Button.
View your new Index Sheet now;
All should be there including a auto-numbering of them.

Now, On any Cell (on the ws Index) containing a ws name, Double-Click the
name.

Will that work for you?
Even if your now out at sheet 99 XYZ, you can click
on the |< button and then see the Index sheet in order to quickly get back
to it.

Hope this helps,
Jim May
 
Top