Current Special! Complete Excel
for Excel 97 - Excel 2003, only
30 Day Money Back Guarantee & Free
Excel Help for LIFE!
Got any Excel Questions? Free Excel Help
When working with Excel, many
users end up with a lot of different Worksheets which then makes navigation
cumbersome. This can be over come in a few ways, with the best being keeping
the number of Worksheets in an Excel Workbook to a minimum. This not only
helps with navigation, but also means a far more
. Ideally one should store all raw data on one
Worksheet, in a classic table format (headings across the top and data laid
out beneath) and then use another Worksheet to extract, report, chart and
filter out needed data. However, sometimes this is just not possible for
many reasons which is why we will see how we can work-around by creating an
Before we jump into that, let's first ensure you are aware of Excel's built in pop-up sheet index. To see this simply right click on the sheet scroll tabs to the left of the name tabs. By doing this you will see a pop-up menu like shown below;
Upon clicking "More sheets..." you will see all Worksheets listed in a nice scrollable ListBox as shown below
If this is still not what you need we can create a Worksheet Index. First add a new sheet to the Workbook and call it "Index" (optional). Next right click on the sheet name tab of the "Index" sheet and select "View Code". In here you should paste the code as shown below
Private Sub Worksheet_Activate() Dim wSheet As Worksheet Dim l As Long l = 1 With Me .Columns(1).ClearContents .Cells(1, 1) = "INDEX" .Cells(1, 1).Name = "Index" End With For Each wSheet In Worksheets If wSheet.Name <> Me.Name Then l = l + 1 With wSheet .Range("A1").Name = "Start_" & wSheet.Index .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _ SubAddress:="Index", TextToDisplay:="Back to Index" End With Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _ SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name End If Next wSheet End Sub
Now click the top right X to get back to Excel proper and then Save. Now, each time you activate the Index sheet the old list will be cleared and a new one will be added. This ensures the index shown is always up-to date should you add or delete Worksheet. You will also note that a range based on the Worksheet name is added to each Worksheet in range A1. If this clashes with existing names, simply change;
.Range("A1").Name = "Start " & wSheet.Index AND Anchor:=.Range("A1")
.Range("B1").Name = "Start " & wSheet.Index AND Anchor:=.Range("B1")
Or any other cell. You will also note that on each Worksheet is a "Back To Index" hyperlink
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates