Navigatng worksheets in a large Workbook

Is there a shortcut to go back and forth to worksheets that are used often 
but not readily available. For exam;ple sheet 25,28,31etc.
0
12/21/2005 6:59:02 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
597 Views

Similar Articles

[PageSpeed] 29

Hello, you could try right clicking on the arrows at the lower left hand side 
of the sheet. This should show all sheets in your workbook,
Regards, Nick.


"Trying To Excel" wrote:

> Is there a shortcut to go back and forth to worksheets that are used often 
> but not readily available. For exam;ple sheet 25,28,31etc.
0
PirateSam (4)
12/21/2005 9:10:02 AM
With a large 500 sheet workbook an index page has proved invaluable.  

On one sheet (called 'GoToSheet') links have been created to all 50
pages. EG

=HYPERLINK("#Sheet1!A1",1)
=HYPERLINK("#Sheet2!A1",2)
=HYPERLINK("#Sheet3!A1",3)
=HYPERLINK("#Sheet4!A1",4)
=HYPERLINK("#Sheet5!A1",5)
=HYPERLINK("#Sheet6!A1",6)
and so on up to 
=HYPERLINK("#Sheet500!A1",500)

A return link to the index page is put on each of the 500 sheets t
both the index page and a summary
=HYPERLINK("#Summary!A1","Summary")
=HYPERLINK("#GoToSheet!A1","GoToSheet")

To create the links, entered the various items in separate columns
incremented the sheet and description columns and then concatenate
across all the columns to create the 500 links, copied the links an
pasted into the 'GoToSheet' Sheet.

Manually created the return links (for those who didn't like using F5
on Sheet1, copied and then pasted across all remaining 499 sheets a
once.

There are 'VBA
(http://www.exceltip.com/st/Selecting_a_Sheet_from_a_Sorted_Sheets_List/645.html
solutions which I tried, but I prefer the index page

--
kghexc
-----------------------------------------------------------------------
kghexce's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2980
View this thread: http://www.excelforum.com/showthread.php?threadid=49511

0
12/21/2005 9:41:05 AM
How about a toolbar that you can use with any workbook?

If you want to try one, start a new workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like:   VBAProject (yourfilename.xls)  

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side.  Paste this code
there.

Option Explicit
Sub auto_close()
    On Error Resume Next
    Application.CommandBars("MyNavigator").Delete
    On Error GoTo 0
End Sub

Sub auto_open()

    Dim cb As CommandBar
    Dim ctrl As CommandBarControl
    Dim wks As Worksheet
    
    On Error Resume Next
    Application.CommandBars("MyNavigator").Delete
    On Error GoTo 0
    
    Set cb = Application.CommandBars.Add(Name:="myNavigator", temporary:=True)
    With cb
        .Visible = True
        Set ctrl = .Controls.Add(Type:=msoControlButton, temporary:=True)
        With ctrl
            .Style = msoButtonCaption
            .Caption = "Refresh Worksheet List"
            .OnAction = ThisWorkbook.Name & "!refreshthesheets"
        End With
        
        Set ctrl = .Controls.Add(Type:=msoControlComboBox, temporary:=True)
        With ctrl
            .Width = 300
            .AddItem "Click Refresh First"
            .OnAction = ThisWorkbook.Name & "!changethesheet"
            .Tag = "__wksnames__"
        End With
    End With

End Sub
Sub ChangeTheSheet()

    Dim myWksName As String
    Dim wks As Worksheet

    With Application.CommandBars.ActionControl
        If .ListIndex = 0 Then
            MsgBox "Please select an existing sheet"
            Exit Sub
        Else
            myWksName = .List(.ListIndex)
        End If
    End With
        
    Set wks = Nothing
    On Error Resume Next
    Set wks = Worksheets(myWksName)
    On Error GoTo 0
    
    If wks Is Nothing Then
        Call RefreshTheSheets
        MsgBox "Please try again"
    Else
        wks.Select
    End If
    
End Sub
Sub RefreshTheSheets()
    Dim ctrl As CommandBarControl
    Dim wks As Worksheet
    
    Set ctrl = Application.CommandBars("myNavigator") _
                      .FindControl(Tag:="__wksnames__")
    ctrl.Clear
    
    For Each wks In ActiveWorkbook.Worksheets
        If wks.Visible = xlSheetVisible Then
            ctrl.AddItem wks.Name
        End If
    Next wks
End Sub

Now back to excel and
file|saveas
choose save as type:  Microsoft Office Excel Add-in (*.xla)
at the bottom of that dropdown.

Now close excel and reopen it.
Turn on the addin.
tools|addins
look for that workbookname you just created and put a check mark there.

You should see a toolbar that you can position where you want.

If you swap workbooks, just click the other button to get a fresh list of
worksheet names in the dropdown.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Trying To Excel wrote:
> 
> Is there a shortcut to go back and forth to worksheets that are used often
> but not readily available. For exam;ple sheet 25,28,31etc.

-- 

Dave Peterson
0
petersod (12004)
12/21/2005 3:44:08 PM
One more from Bob Phillips

Sub BrowseSheets()
Const nPerColumn  As Long = 38          'number of items per column
Const nWidth As Long = 13                'width of each letter
Const nHeight As Long = 18              'height of each row
Const sID As String = "___SheetGoto"    'name of dialog sheet
Const kCaption As String = " Select sheet to goto"
                                        'dialog caption

Dim i As Long
Dim TopPos As Long
Dim iBooks As Long
Dim cCols As Long
Dim cLetters As Long
Dim cMaxLetters As Long
Dim cLeft As Long
Dim thisDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As OptionButton

    Application.ScreenUpdating = False

    If ActiveWorkbook.ProtectStructure Then
        MsgBox "Workbook is protected.", vbCritical
        Exit Sub
    End If

    On Error Resume Next
        Application.DisplayAlerts = False
        ActiveWorkbook.DialogSheets(sID).Delete
        Application.DisplayAlerts = True
    On Error GoTo 0
    Set CurrentSheet = ActiveSheet
    Set thisDlg = ActiveWorkbook.DialogSheets.Add

    With thisDlg

        .Name = sID
        .Visible = xlSheetHidden

        'sets variables for positioning on dialog
        iBooks = 0
        cCols = 0
        cMaxLetters = 0
        cLeft = 78
        TopPos = 40

        For i = 1 To ActiveWorkbook.Worksheets.Count

            If i Mod nPerColumn = 1 Then
                cCols = cCols + 1
                TopPos = 40
                cLeft = cLeft + (cMaxLetters * nWidth)
                cMaxLetters = 0
            End If

            Set CurrentSheet = ActiveWorkbook.Worksheets(i)
            cLetters = Len(CurrentSheet.Name)
            If cLetters > cMaxLetters Then
                cMaxLetters = cLetters
            End If

            iBooks = iBooks + 1
            .OptionButtons.Add cLeft, TopPos, cLetters * nWidth, 16.5
            .OptionButtons(iBooks).text = _
                ActiveWorkbook.Worksheets(iBooks).Name
            TopPos = TopPos + 13

        Next i

        .Buttons.Left = cLeft + (cMaxLetters * nWidth) + 24

        CurrentSheet.Activate

        With .DialogFrame
            .Height = Application.Max(68, _
                Application.Min(iBooks, nPerColumn) * nHeight + 10)
            .Width = cLeft + (cMaxLetters * nWidth) + 24
            .Caption = kCaption
        End With

        .Buttons("Button 2").BringToFront
        .Buttons("Button 3").BringToFront

        Application.ScreenUpdating = True
        If .Show Then
            For Each cb In thisDlg.OptionButtons
                If cb.Value = xlOn Then
                    ActiveWorkbook.Worksheets(cb.Caption).Select
                    Exit For
                End If
            Next cb
        Else
            MsgBox "Nothing selected"
        End If

        Application.DisplayAlerts = False
        .Delete

    End With

End Sub



Gord Dibben Excel MVP

On Tue, 20 Dec 2005 22:59:02 -0800, Trying To Excel
<TryingToExcel@discussions.microsoft.com> wrote:

>Is there a shortcut to go back and forth to worksheets that are used often 
>but not readily available. For exam;ple sheet 25,28,31etc.
0
Gord
12/21/2005 6:59:33 PM
Reply:

Similar Artilces:

Cells that move with the rest of the worksheet
How do you do this? I know it is possible from when I took a class in Excel, but I can't remember how to do it. Perhaps you're referring to what happens visually on screen when you: Click Insert > Rows Click Insert > Columns (Just a guess <g>) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Brandon" <deanfamily@erols.com> wrote in message news:#uKXR$ZuFHA.2072@TK2MSFTNGP14.phx.gbl... > How do you do this? I know it is possible from when I took a class in > Excel, but I can't remember how to do it....

how to use a data validation list from another worksheet within the same workbook
does anyone know how us a data validation list from another worksheet within the same workbook? mp, use a named range, have a look here for how to do it http://www.contextures.com/xlDataVal01.html#Name -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "mp" <mikeplanting@gmail.com> wrote in message news:1160751757.346757.183920@k70g2000cwa.googlegroups.com... > does anyone know how us a data validation list from anoth...

Worksheet data sorted on another worksheet
All, Can't seem to get this going. Have some data on 1 worksheet, named cgg, 25 columns, 25 rows. Would like to have this same data but sorted on column 3 on another worksheet called cggsorted. Any idea how best to achieve this, thanks, Martin Martin, Enter a new column A on sheet gcc, and in that coulmn, enter RANK functions to rank the value in column 3, to match the size of your table: =RANK(C1,$C$1:$C$25,TRUE) or =RANK(C1,$C$1:$C$25,FALSE) (TRUE or FALSE depends on how you want the data sorted, ascending or descending). You also need to decide how to handle ties.... A...

If Worksheet function
I am using the IF worksheet function to do a vlookup to obtain certai values for an entered cell. I am limited to 7 by default. is ther another may to obtain this data. I have 9 different input options t obtain several output -- Lin ----------------------------------------------------------------------- Lins's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2422 View this thread: http://www.excelforum.com/showthread.php?threadid=48941 On Wed, 30 Nov 2005 08:36:27 -0600, Lins <Lins.1zbdsm_1133361600.7943@excelforum-nospam.com> wrote: > >I am using th...

worksheet nested if
i am trying to write an if statment that will do the following if c8="p" then y20 if c9="p" then y21 if c8&c9="p" then y20+y21 if c8="c" then nil if c9="c" then nil this statment works for the C8 & C9 inputs from strategy sheet 1 at a time but i am having problems with the sum part =IF('Strategy Evaluation'!C8="P",Y20*AB20-AD9,IF('Strategy Evaluation'!C9="P",Y21*AB21-AD9," ")) =IF('Strategy Evaluation'!C8="P",Y20*AB20-AD9,IF('Strategy Evaluation'!C9="P&q...

Excel macro for copying range to another worksheet
On a monthly basis, I would like to copy the completed range (varies from month to month) of a database (Sheet1) to a master list (Sheet3). Once the data has been copied I intend to manually delete the entries of Sheet1 and start anew for the new month =96 for eventual transfer to Sheet3. The idea is to copy each month=92s data at the bottom of the previous months=92 (Sheet3). I followed Excel=92s record macro command but the macro I ended up with is not capable of placing the new data at the bottom of the existing one; it simply keeps overwriting the previous entry. Unfortunately, I don=92t...

Printing a selected worksheet
I have multiple worksheets to select from, but the user will only need to print one of those sheets. How can I in code through an input box function ask the user to input the sheet name that is needed to be printed? I have just checked for the last hour hunting for an answer, so sorry if this question has been asked before. prtFile = InputBox("Enter a file name (with/without extension?)", "FILE NAME") Workbooks(prtFile).PrintOut "caldog" <caldog@discussions.microsoft.com> wrote in message news:10B0FCC1-789F-4714-8604-7D789CDED2...

updating multiple worksheets
I have 2 worksheets for attendance. 1-15th and 16-31st. I want to be able to make a change to EITHER sheet which will update BOTH sheets. Is this possible? -- Thanks, Pat If you group the worksheets, any change made to one will be made to the other. Select the first worksheet tab, hold down the CTRL key and click the second worksheet. The word 'Group' will be displayed in the Application Caption. Be sure to ungroup the sheets when you're done. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Pat" <Pat@di...

Excel Formula
Hello All - I am new to Excel, and am used to working on only worksheet. I would greatly appreciate if someone could provide me wit the formula for the following. I have a new book with 3 worksheets. need to pick a figure from worksheet 1 - entitled "INCOME", cel address P5, and from this, subtract a figure from worksheet 2 - entitled "EXPENSE", cell address Q5, to have the result appear i worksheet 3 - entitled "BANK", cell address F11 -- Message posted from http://www.ExcelForum.com "Craig Lescombe >" <<Craig.Lescombe.10fu3e@excelforum...

can i water mark part of a worksheet
can i water mark part of a worksheet See Watermark http://www.xldynamic.com/source/xld.xlFAQ0005.html and http://office.microsoft.com/en-us/excel/HP012138021033.aspx best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Excell buff" <Excell buff@discussions.microsoft.com> wrote in message news:D0CD4BFC-64B9-4FAC-8808-35D1ED22BD5A@microsoft.com... > can i water mark part of a worksheet ...

Merge Worksheets
Hello, I have a macro that will merge my worksheets into one worksheet called "Master". It works fine. Is it possible when I merge my worksheets again to create another worksheet called "Master1"..."Master2"..."Master3"...and so on without overwriting or deleting the previous "Master" worksheet? The workbook is used in Share Mode. Here is the macro that I use: Sub CopyFromWorksheets() Dim wrk As Workbook 'Workbook object - Always good to work with object variables Dim sht As Worksheet 'Object for handling worksheets i...

Worksheets within a worksheet
I need to do a separate worksheet for each State, which I can do, but then within each State worksheet, have a separate worksheet for each month. And be able to tab through each monthly worksheet for that State. Is this possible? Need a seperate file for each state to do this "Casey" <Casey@discussions.microsoft.com> wrote in message news:CD2DF476-F369-45B0-BB61-94C1E3831A07@microsoft.com... : I need to do a separate worksheet for each State, which I can do, but then within each State worksheet, have a separate worksheet for each month. And be able to tab through each mon...

Change Startup workbook
I created a workbook template with all the formats, logos, layouts, etc that I want to use most of the time. I want this to be the default workbook for NEW or on opening EXCEL. I have put copies in the MY TEMPLATES folder, OFFICE>STARTUP>EXCEL both as Workbook.xlt and Workbook.xls It still defaults to the Microsoft default template. Help Geno Save it as BOOK.XLT in the folder XLStart: http://office.microsoft.com/en-us/excel/HP101974891033.aspx HTH, Bernie MS Excel MVP "Gene Augustin" <gene@wirelessengineeringcorp.com> wrote in message news:C5AF1FAB.4690%gene...

Copy formulas from one workbook to another
I'm using Excel 2007. I wanted to copy some formulas from one workbook to another, but it becomes pictures right away (same as choices when pictures). The paste functions work fine within the same worksheet or the same workbook. It never happened before. Can someone help? Thanks. Need additional information. Example of formula you are trying to copy will help greatly. -- tech1NJ "Ming" wrote: > I'm using Excel 2007. I wanted to copy some formulas from one workbook to > another, but it becomes pictures right away (same as choices when pictur...

Hide worksheets before close
I have a workbook with multiple sheets that I would like to Hide before close. I am using a macro attached to a "Quit" button, but it does not work. I also have a Sub in ThisWorkbook, but I can't seem to call it Sub Button14_Click_Quit() Application.DisplayAlerts = False Worksheets("A Detail").Visible = False Worksheets("A Metrics").Visible = False Worksheets("B DV Detail").Visible = False Worksheets("B DV Metrics").Visible = False Worksheets("B Detail").Visible = False Worksheets("B Metrics&qu...

Select a worksheet
I'm using the below formula to determine the specific type of equipment from a list named: database =IF(VLOOKUP($J$2,Database!$A:$U,21,FALSE)=$X$2,"Desktop",IF(VLOOKUP($J$2,Database!$A:$U,21,FALSE)=$X$5,"Notebook","Error!!!!!!!")) value of X2 = DSK and value of X5 = NBK What I need help with is a way of using the value returned to automatically open the relevant worksheet, ie: If "Desktop" is returned the Desktop worksheet needs to open and if "Notebook" is returned the notebook worksheet needs to open automatically. All wo...

... How do I Hyperlink to worksheet of an Excel file ...
I have built a worksheet which I want to be a directory to fourteen worksheets of another Excel File. The Hyperlinks open the file to the last saved window of the file. How do I link to only a specific worksheet? Darrell Are you using Insert|Hyperlink to create the hyperlink? If I point to: Book1.xls and add the address I want to go to so it looks like: Book1.xls#sheet2!a5 It works ok. A tip. If you may be inserting rows/columns in that worksheet, you may want to give that cell a name and use that. Otherwise, that link will go to sheet2!a5 no matter if that's really the cell...

Working with Different workbooks
Dear Receipient, I would like to know how to open two different excel files in two different workbooks, in other words... I want to see both files on the taskbar and not only one of them and the other is stacked somewhere behind it. Thanks and Regards, Menu Tools>Options>View and check the Windows In Taskbar checkbox. -- HTH RP (remove nothere from the email address if mailing direct) "eawinga" <eawinga@discussions.microsoft.com> wrote in message news:CFE4564D-6EB4-48AE-A297-A2DE99624C01@microsoft.com... > Dear Receipient, > > I would l...

Navigatng worksheets in a large Workbook
Is there a shortcut to go back and forth to worksheets that are used often but not readily available. For exam;ple sheet 25,28,31etc. Hello, you could try right clicking on the arrows at the lower left hand side of the sheet. This should show all sheets in your workbook, Regards, Nick. "Trying To Excel" wrote: > Is there a shortcut to go back and forth to worksheets that are used often > but not readily available. For exam;ple sheet 25,28,31etc. With a large 500 sheet workbook an index page has proved invaluable. On one sheet (called 'GoToSheet') links have ...

Master worksheet automatically enters data into sub worksheets
I've seen some similar postings but not specific enough. I have a master worksheet with a lot of data. There are four sub area worksheets that are blank. All data is kept on the master. Data only goes into a sub worksheet as needed, so all data does not go into all the sub worksheets. All worksheets have one common column that has a unique identifier for that row of data. I need to be able to take the unique identifier from the Master worksheet and enter the identifier on a sub worksheet and all matching column entries for that identifier's row to enter on the row on the sub wor...

dynamic reference to data in multiple closed workbooks
Hi, I'm working with a job list (generated out of a different program) and data in associated closed workbooks. For example, the job list has names like HD-100311-TA031110, SHT-100312, 032110Mag and I can generate a list of these jobs along with other information I need. For every job there's an associated workbook and, without opening the workbook I want to pull, for example, '[\\de-mt1\clients\QC Reports\Proofs Adjusted\HD-100311-TA031110 proof adjusted.xls]'Upload'!A$2 where the only information that will change is the job name. I'm novice enough not to...

Worksheet Protection Crashes Excel
Hi, I have a worksheet protected with locked and unlocked cells. I can open the file, and can navigate left to right. When I try to tab to the first cell on the next line, Excel crashes. When I unprotect the sheet, I have no problems. The worksheet has a couple hidden columns and a couple hidden rows. The worksheet (and workbook) contain no macros. Any thoughts on resolving this? Henry ...

Debra Dalgleish-help with worksheet data entry
Hi Debra, I browsed your website and found this new data entry worksheet(http://contextures.com/xlForm02.html) which was very good and i showed my boss.He liked the idea of it and thus asked me to work on it.I managed to add a few more fields and it worked fine. I have a cell named "Documentation to support requirement" at B11 and its corresponding text box at D11.My problem now is that at D11, i need two kind of controls one is drop down list box for document type and another control is text box for document name.How do i achieve this? i need both of this control's answer to b...

Incompatible worksheet
I use excel 97. I downloaded a worksheet which includes a cell for downloading share prices from msn money, which was created in a later version. I saved it as a xl97 worksheet, but the weblink cell was obviously not available, as xl 'encountered an error' and shutdown. However, it does work sometimes. Is there any way I can get it to work everytime? (apart from upgrading my version of xl) Brickcounter ...

My CD won't let me save my Excel workbook on them?
I have created Excel workbooks but when they are saved under excel workbook and I put in or have already have in a CD for saving on, My Excel says that my computer can not format this kind of disc. I have had these discs before and have saved on them many times. I recently bought CD-RW so that I could erase and use again. My computer won't let me. I am sooo frustrated. I have been up late for 3 wks. trying to figure this thing out. Why when I put in a CD of ANY KIND, does it automatically go to Media Player and fill up my cd before I can even save anything on them? Whe...