Reference isn't valid - VBA generated hyperlink

[mysticmario]

Hi,
I used this code previously on with anoterh application, when i tried to replicate this for new usage I get the reference isnt valid error and for the love of god I cannot get it to work
here's the code:

Dim Ws As Worksheet, lrQ As Long
Dim wb As Workbook
Set wb = ThisWorkbook
Dim adressn As String

    For Each Ws In Worksheets
        If InStr(Ws.Name, "Witold_") Then
        adressn = Ws.Name
        Set Ws = wb.Worksheets(adressn)
        lrQ = 6
            Sheets("PANEL WYCEN").Cells(lrQ + 1, "Q").Hyperlinks.add Anchor:=Sheets("PANEL WYCEN").Cells(lrQ + 1, "Q"), Address:="", SubAddress:=adressn & "!A1", TextToDisplay:=adressn
            lr = lr + 1
        End If
    Next Ws

Any Idea why this happens?

 

[smozgur]

Some "Witold_" worksheets should have space in the name. That's the reason of the error when you click on the generated link.
Use the following code line instead. Note that I wrapped the addresn with single quotes to make it work correctly.

Sheets("PANEL WYCEN").Cells(lrQ + 1, "Q").Hyperlinks.Add Anchor:=Sheets("PANEL WYCEN").Cells(lrQ + 1, "Q"), Address:="", SubAddress:="'" & adressn & "'!A1", TextToDisplay:=adressn

Also, although these are not causing any errors:

• The lr variable name should be lrQ after that line.
• lrQ = 6 line above that line should be moved out of the loop (just before the loop is fine). Otherwise, all links will be created in cell Q6 since lrQ will be always assigned as 6 in the loop.

Something like this after all suggested changes. I suggest using Option Explicit to avoid such declaration mistakes.

Option Explicit

Sub MacroName()
Dim Ws As Worksheet, lrQ As Long
Dim wb As Workbook
Set wb = ThisWorkbook
Dim adressn As String
    lrQ = 6
    For Each Ws In Worksheets
        If InStr(Ws.Name, "Witold_") Then
        adressn = Ws.Name
        Set Ws = wb.Worksheets(adressn)
            Sheets("PANEL WYCEN").Cells(lrQ + 1, "Q").Hyperlinks.Add Anchor:=Sheets("PANEL WYCEN").Cells(lrQ + 1, "Q"), Address:="", SubAddress:="'" & adressn & "'!A1", TextToDisplay:=adressn
            lrQ = lrQ + 1
        End If
    Next Ws
End Sub

 

[mysticmario]

Some "Witold_" worksheets should have space in the name. That's the reason of the error when you click on the generated link.
Use the following code line instead. Note that I wrapped the addresn with single quotes to make it work correctly.

Sheets("PANEL WYCEN").Cells(lrQ + 1, "Q").Hyperlinks.Add Anchor:=Sheets("PANEL WYCEN").Cells(lrQ + 1, "Q"), Address:="", SubAddress:="'" & adressn & "'!A1", TextToDisplay:=adressn

Also, although these are not causing any errors:

• The lr variable name should be lrQ after that line.
• lrQ = 6 line above that line should be moved out of the loop (just before the loop is fine). Otherwise, all links will be created in cell Q6 since lrQ will be always assigned as 6 in the loop.

Something like this after all suggested changes. I suggest using Option Explicit to avoid such declaration mistakes.

Option Explicit

Sub MacroName()
Dim Ws As Worksheet, lrQ As Long
Dim wb As Workbook
Set wb = ThisWorkbook
Dim adressn As String
    lrQ = 6
    For Each Ws In Worksheets
        If InStr(Ws.Name, "Witold_") Then
        adressn = Ws.Name
        Set Ws = wb.Worksheets(adressn)
            Sheets("PANEL WYCEN").Cells(lrQ + 1, "Q").Hyperlinks.Add Anchor:=Sheets("PANEL WYCEN").Cells(lrQ + 1, "Q"), Address:="", SubAddress:="'" & adressn & "'!A1", TextToDisplay:=adressn
            lrQ = lrQ + 1
        End If
    Next Ws
End Sub

Than you the codefor hyperlinks now works great.
As for the later part, I've noticed that aswell and I fixed that soon after posting the issue.
Thank you for your help

 

[smozgur]

Than you the codefor hyperlinks now works great.
As for the later part, I've noticed that aswell and I fixed that soon after posting the issue.
Thank you for your help

Glad to hear it helps!