creating an index page and referencing cells within those pages
So, I've seen several ways to create an index page in Excel by referencing tab names, but I would also like to include information in certain cells on that main index page that are dynamic so that they will update when I update information.
I keep client lists in Excel.
The tab name is the client name, which is what appears on the index page, but, within those tabs, I have a cell (B1) that contains the status of that client and another cell (B2) that tells me when I need to follow up with them.
I would like both of these cells to be included on the index page so I can quickly look down the list without having to go through every tab.
Currently I am using this code as a TOC
'Declare all variables
Dim ws As Worksheet, curws As Worksheet, shtName As String
Dim nRow As Long, i As Long, N As Long, x As Long, tmpCount As Long
Dim cLeft, cTop, cHeight, cWidth, cb As Shape, strMsg As String
Dim cCnt As Long, cAddy As String, cShade As Long
'Check if a workbook is open or not.
If no workbook is open, quit.
If ActiveWorkbook Is Nothing Then
MsgBox "You must have a workbook open first!", vbInformation, _
"No Open Book"
cShade = 37 '<<== SET BACKGROUND COLOR DESIRED HERE
'Turn off events and screen flickering.
Application.ScreenUpdating = False
Application.DisplayAlerts = False
nRow = 4: x = 0
'Check if sheet exists already; direct where to go if not.
On Error GoTo hasSheet
'Confirm the desire to overwrite sheet if it exists already.
If MsgBox("You already have a Table of Contents page." _
& vbLf & vbLf & _
"Would you like to overwrite it?", _
vbYesNo + vbQuestion, "Replace TOC page?") = vbYes Then GoTo createNew
x = 1
'Add sheet as the first sheet in the workbook.
'Reset error statment/redirects
On Error GoTo 0
'Set chart sheet varible counter
tmpCount = ActiveWorkbook.Charts.Count
If tmpCount > 0 Then tmpCount = 1
'Set a little formatting for the TOC sheet.
ActiveSheet.Name = "TOC"
.Cells.Interior.ColorIndex = cShade
.Rows("4:65536").RowHeight = 16
.Range("A1").Value = "Designed by VBAX"
.Range("A1").Font.Bold = False
.Range("A1").Font.Italic = True
.Range("A1").Font.Name = "Arial"
.Range("A1").Font.Size = "8"
.Range("A2").Value = "Table of Contents"
.Range("A2").Font.Bold = True
.Range("A2").Font.Name = "Arial"
.Range("A2").Font.Size = "24"
'Set variables for loop/iterations
N = ActiveWorkbook.Sheets.Count + tmpCount
If x = 1 Then N = N - 1
For i = 2 To N
shtName = Sheets(i).Name
'Add a hyperlink to A1 of each sheet.
.Range("C" & nRow).Hyperlinks.Add _
Anchor:=.Range("C" & nRow), Address:="#'" & _
shtName & "'!A1", TextToDisplay:=shtName
.Range("C" & nRow).HorizontalAlignment = xlLeft
.Range("B" & nRow).Value = nRow - 2
nRow = nRow + 1
'Perform some last minute formatting.
'Turn events back on.
Application.DisplayAlerts = True
Application.ScreenUpdating = True
strMsg = vbNewLine & vbNewLine & "Please note: " & _
"Charts will have hyperlinks associated with an object."
'Toggle message box for chart existence or not, information only.
If cCnt = 0 Then strMsg = ""
MsgBox "Complete!" & strMsg, vbInformation, "Complete!"
Keys to the Problem creating an index page and referencing cells within those pages
Make Sure that your Computer Meets the System Requirements
You need to double-check your computer's hardware configuration against the following Microsoft requirements. If your computer hardware is not up to par, then make sure you upgrade where necessary before continuing:
Microsoft Windows Requirements :
- 1 gigahertz (GHz) or faster 32-bit (x86) or 64-bit (x64) processor
- 1 gigabyte (GB) RAM (32-bit) or 2 GB RAM (64-bit)
- 16 GB available hard disk space (32-bit) or 20 GB (64-bit)
If your computer meets the minimum requirements above, then continue on…
Another Safe way to Repair the Problem: creating an index page and referencing cells within those pages:
How to Fix creating an index page and referencing cells within those pages with SmartPCFixer?
1. Download SmartPCFixer . Install it on your computer. Click Scan, and it will perform a scan for your computer. The junk files will be shown in the scan result.
2. After the scan is finished, you can see the errors and problems which need to be fixed.
3. The Repair part is finished, the speed of your computer will be much higher than before and the errors have been fixed. You can also use other functions in SmartPCFixer. Like dll downloading, windows updating and print spooler error repair.
Related: How Can You Update & Download NVidia 6100, 6800 GS/XT Display Driver v.260.89 WHQL,Where to Download NVidia GeForce 8800 GT WHQL-certified driver v.196.21,How to Update & Download NVidia GeForce Go 7400 Display Driver v.260.19.12,[Solved] Download NVidia GeForce GTX 460M Driver v.331.82,Download NVidia Quadro Plex Model IV VGA Driver v.304.43 Certified,Way to Download RealTek RTL8100C(L) Driver v.5.01,Way to Download RealTek RTL8100E Drivers v.694,Way to Update & Download RealTek RTL8101L Auto Installation Program v.6.110 driver,Method to Update & Download RealTek RTL8111G PXE and RPL ROM code v.2.58 driver,How to Update & Download RealTek RTL8411B(N) Driver v.10.003,Best Way to Update & Download ASUS A53SV nVidia Graphics Driver v.22.214.171.12486,Method to Herunterladen ASUS K75VJ Intel Rapid Storage Technology Treiber v.126.96.36.1990,How Can You Update & Download ASUS CG8580 Intel Chipset Driver v.188.8.131.529,Method to Update & Download ASUS K41VD Intel INF Update Driver v.184.108.40.2065,Way to Update & Download ASUS Pro70T NB Probe v.3.0.0026 driver
Read More: Solution to Error: convert a cells text to basic html code,[Solution] consantly getting message "not responding" how can I correct this? My computer freezes.,Troubleshooting:Constant DNS Server problems Error,context menu problem,Troubleshoot:count unique dates Error,Closing Internet Explorer causes error,Compare 2 Tables and Calculate the PROFIT Faster,Computer goes to Windows Boot Manager after Start,Connect to one of my network computer remotely when don't allow exception box Marked,computer locking up randomly