C
c1802362
When I create a file with lots of worksheets, I'll often run the following code to create an index sheet as the first tab (I also run some code that inserts the 'home' icon with a return to this page on each individual sheet).
It works great for worksheets, but I've been unable to get it to work for chart sheets. Any suggestions?
Art
**********************************
Dim shtTarget As Worksheet, iCount As Integer
iCount = 1
Cells(1, 1) = "Sheet"
Cells(1, 2) = "Name"
Cells(1, 3) = "Sheet Status"
For Each shtTarget In Worksheets
Cells(iCount + 1, 1) = iCount
ActiveSheet.Hyperlinks.Add Anchor:=Cells(iCount + 1, 2), Address:="", _
SubAddress:="'" & shtTarget.Name & "'!A1", TextToDisplay:=shtTarget.Name
If shtTarget.Visible = True Then
Cells(iCount + 1, 3) = "Visible"
Else
Cells(iCount + 1, 3) = "Hidden"
Cells(iCount + 1, 3).Font.ColorIndex = 3
End If
iCount = iCount + 1
Next shtTarget
End Sub
It works great for worksheets, but I've been unable to get it to work for chart sheets. Any suggestions?
Art
**********************************
Dim shtTarget As Worksheet, iCount As Integer
iCount = 1
Cells(1, 1) = "Sheet"
Cells(1, 2) = "Name"
Cells(1, 3) = "Sheet Status"
For Each shtTarget In Worksheets
Cells(iCount + 1, 1) = iCount
ActiveSheet.Hyperlinks.Add Anchor:=Cells(iCount + 1, 2), Address:="", _
SubAddress:="'" & shtTarget.Name & "'!A1", TextToDisplay:=shtTarget.Name
If shtTarget.Visible = True Then
Cells(iCount + 1, 3) = "Visible"
Else
Cells(iCount + 1, 3) = "Hidden"
Cells(iCount + 1, 3).Font.ColorIndex = 3
End If
iCount = iCount + 1
Next shtTarget
End Sub