I Need to Speed Up My Computer: How to Fix Error - creating an index page and referencing cells within those pages?

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.
For example,
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.
Thank you!

Currently I am using this code as a TOC

Sub CreateTOC()
'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"
Exit Sub
End If
'--------------------------------------------------------
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
Sheets("TOC").Activate
'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
Exit Sub
hasSheet:
x = 1
'Add sheet as the first sheet in the workbook.
Sheets.Add before:=Sheets(1)
GoTo hasNew
createNew:
Sheets("TOC").Delete
GoTo hasSheet
hasNew:
'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"
With Sheets("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"
.Range("A4").Select
End With
'Set variables for loop/iterations
N = ActiveWorkbook.Sheets.Count + tmpCount
If x = 1 Then N = N - 1
For i = 2 To N
With Sheets("TOC")
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
End With
continueLoop:
Next i
'Perform some last minute formatting.
With Sheets("TOC")
.Range("C:C").EntireColumn.AutoFit
.Range("A4").Activate
End With
'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!"
End Sub

Keys to the Problem creating an index page and referencing cells within those pages

Download Error Fixer for Free Now

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.

I Need to Speed Up My Computer

Friday, September 23, 2016

How to Fix Error - creating an index page and referencing cells within those pages?

creating an index page and referencing cells within those pages

Keys to the Problem creating an index page and referencing cells within those pages

Make Sure that your Computer Meets the System Requirements

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?

No comments:

Post a Comment