asked on

Build index including links

Hi all

I have a workbook with some VBA that create an index with all the sheet names from the workbook. It also create hyperlinks to each sheet.

My problem occours when I rename a sheet after the index is build. Even if I re-build the index I get the "reference is not valid error".

I hope you guys can help me out with the code, which looks like this:

x = 11 
Sheet4.Range("B11:B34").Clear 
For Each ws In Worksheets 
    If Not ws.Name = "Template" Then ' The Template sheet is hidden and should not be included in the index
        Sheet4.Cells(x, 2) = ws.Name 
        Sheet4.Select 
        ActiveSheet.Hyperlinks.Add Anchor:=Cells(x, 2), Address:="", SubAddress:=ws.Name, TextToDisplay:=ws.Name 
        x = x + 1 
    End If 
Next ws

Open in new window

BR Michael

Steve

in subAddress try adding a cell reference:

ws.name&"!A1"

Exceldoctor

ASKER

Thx Barman
I just tried this. It has no effect on the sheets that returned the error. For the working links it works perfectly linking to cell A1 in the destination sheet.

Steve

What are the names of the sheets in error?
Are they charts or other non normal sheets?
Would it be possible to post the workbook here?

Steve

I have tested the following code and it seems to work fine.

x = 11
Sheet4.Range("B11:B34").Clear
For Each ws In ThisWorkbook.Worksheets
    If Not ws.Name = "Template" Then ' The Template sheet is hidden and should not be included in the index
        Sheet4.Hyperlinks.Add Anchor:=Cells(x, 2), Address:="", SubAddress:=ws.Name & "!A1", TextToDisplay:=ws.Name
        x = x + 1
    End If
Next ws

Open in new window

Exceldoctor

ASKER

Hi

I did a bit of testing....it seems that the problem can be eliminated if I dont use spaces when I rename the sheet. This is good news, but i would really prefer to allow the users of this sheet to use any sheet name convention they would like to.

Any ideas?

ASKER CERTIFIED SOLUTION

Steve

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Exceldoctor

ASKER

Hey that did the trick. Thx very much :-)