List Excel Sheet & Chart Tabs

Question

This lists & hyperlinks, all Sheets then all Charts. What I would like is for the list to be in the order they are found in the workbook; i.e., sheet1, sheet2, chart1, sheet3, chart 2, etc. I have 126 tabs so a manual reorder isn't an option.

I'm sure there is a way to use an IF/OR but I have no idea how to do the THEN.

Sub CreateLinksToAll()
Dim sh As Worksheet
Dim ch As Chart

Application.ScreenUpdating = False
Sheets("Index").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A2").Select

   For Each sh In ActiveWorkbook.Worksheets
     If ActiveSheet.Name <> sh.Name And sh.Visible Then
       ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
       "'" & sh.Name & "'" & "!A1", TextToDisplay:=sh.Name
       ActiveCell.Offset(1, 0).Select
    End If
   Next sh

   For Each ch In ActiveWorkbook.Charts
     If ActiveSheet.Name <> ch.Name And ch.Visible Then
       ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
       "'" & ch.Name & "'" & "!A1", TextToDisplay:=ch.Name
       ActiveCell.Offset(1, 0).Select
     End If
   Next ch

  Columns("A:A").Font.Size = 12
  Range("A1").Select
End Sub

Did you notice that the link to cell A1 of a chart sheet doesn't work? A chart sheet doesn't have cells to link to. You can read how to get around this in an ancient tutorial, http://peltiertech.com/Excel/Hyperlinks.html. — Jon Peltier, Jan 18 '14 at 17:57

David Zemens · Accepted Answer · 2014-01-17T15:05:14.867

5

You're getting all your worksheets first, and then the charts, because you're doing two iterations over each respective collection.

Instead, iterate over the Workbook's .Sheets collection.

Modified from my original answer: You dont' need any conditional test for Sheet/Chart in this approach. This is untested but I think it should work, let me know if it doesn't (probably missing a parenthesis somewhere...).

Sub Test()

Dim sh As Variant
Dim rng as Range
Dim r as Integer

Application.ScreenUpdating = False
Set rng = Sheets("Index").Range("A2", Range("A2").End(xlDown))
rng.ClearContents
Set rng = rng.Resize(1,1)

For Each sh In ActiveWorkbook.Sheets
   r = r + 1
   rng.Cells(r).Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
       "'" & sh.Name & "'" & "!A1", TextToDisplay:=sh.Name

Next
Application.ScreenUpdating = True
End Sub

edited Jan 17 '14 at 15:05

answered Jan 17 '14 at 03:39

David Zemens

53,033
11
81
130

David a very good suggestion but it doesn't answer the question :) – Siddharth Rout Jan 17 '14 at 03:51
@SiddharthRout they were appearing "out of order" because he was using two loops. I made my original answer in haste but showed how to do it with one loop. I have since revised to avoid the conditional test for Chart/Sheet, since that should not be necessary. – David Zemens Jan 17 '14 at 03:55
+ 1 This make more sense now :) Sorry. Didn't see your edit else I wouldn't have posted an answer. – Siddharth Rout Jan 17 '14 at 03:56
No worries, I think I was editing it while you were commenting :) – David Zemens Jan 17 '14 at 04:08
WOW, you guys are Fantastic! I'll be in the office within the hour and will update the code. Thank you! – Geo Jan 17 '14 at 13:51
The code failed with Syntax error for this line: rng.Resize (1,1) If it is commented out, the code only returns one Chart sheet. – Geo Jan 17 '14 at 15:03
Ooops. Try `Set rng = rng.Resize(1,1)`. – David Zemens Jan 17 '14 at 15:05
Sorry for the dely - work is too busy to answer. Technically it works, meaning it goes through the list, unfortunately, it just keeps updating A2 so I only have one link when it is done. – Geo Jan 18 '14 at 02:49
Never Mind - I added the Activecell.Offset back and it works like a Champ! – Geo Jan 18 '14 at 02:56
New twist, how do I exclude hidden sheet/chart tabs? – Geo Jan 18 '14 at 03:41
You can check `if sh.visible then...` I think that did exclude hidden sheets. – David Zemens Jan 18 '14 at 04:40
1

Thank you. @DavidZemens Works exactly as I needed though I had to make a few changes to your code above: removed Set rng (did not work) and after rng.ClearContents I added Range("A2").Select Then under r =r+1 added the If visible statement and finally added ActiveCell.Offset(1, 0).Select Then End IF above Next. – Geo Jan 19 '14 at 15:40

score 2 · Answer 2 · edited May 23 '17 at 11:52

2

What I would like is for the list to be in the order they are found in the workbook; i.e., sheet1, sheet2, chart1, sheet3, chart 2, etc

For this use a simple For Loop

For i = 1 To ThisWorkbook.Sheets.Count
    Debug.Print ThisWorkbook.Sheets(i).Name
Next

Here I am simply displaying the name of the sheets in the order they are present in the workbook. Simply put your hyperlink code in lieu of that.

EDIT

Also You may find THIS interesting :)

edited May 23 '17 at 11:52

Community

1
1

answered Jan 17 '14 at 03:54

Siddharth Rout

147,039
17
206
250

Thank you. I will be reviewing the link. – Geo Jan 18 '14 at 02:57

List Excel Sheet & Chart Tabs

2 Answers2