VBA error 1004 Application-defined or object-defined error

[bond2020]

I have inherirted an application that has been working up until now using VBA to put hyperlinks on an active worksheet in Excel to other worksheets in the workbook.

I have debugged the code and narrowed it down to the following:

Function ksListSheets()
'===========================================================
' Copy summary AFO data to worksheet
'===========================================================
On Error GoTo Err_ksListSheets

Dim ws As Object
Dim wsMain As Object
Dim intCtr As Integer
Dim intRC1 As Integer

intCtr = 7

With oWkBk.Sheets("MainSheet")
Set wsMain = oWkBk.Sheets("MainSheet")
End With

wsMain.Activate

For Each ws In oWkBk.Worksheets
Select Case ws.Name
Case "MainSheet", "rhTemplate", "rhTemplateAuto", "rhTemplateFire", "rhSummaryData", "rhTeamAFOData"
' do not build hyperlinks for these sheets
Case Else
ActiveSheet.Hyperlinks.Add _
Anchor:=Cells(intCtr, 3), _
Address:="", _
SubAddress:="'" & Replace(ws.Name, "'", "''", 1) & "'!A1", _
TextToDisplay:=ws.Name

intCtr = intCtr + 1
End Select
Next ws

Done_ksListSheets:
ksListSheets = intRC1
Set ws = Nothing
Set wsMain = Nothing
On Error Resume Next
Exit Function

Err_ksListSheets:
MsgBox Err.Number & " " & Err.Description
intRC1 = 16
Resume Done_ksListSheets
End Function

What happens is the Excel workbook is open to the "MainSheet" and is active. It goes through the case once and the ws.Name is set at "MainSheet" so it does nothing and goes to the next worksheet, which is "Zone - Auto". The code then falls through to Case Else and immediately goes to Err-ksListSheets. I have Anchor, SubAddress and TextToDisplay in my watch list and they display "expression not defined in context" in the value field. Again this code had been working up until recently.

Can anyone help. I have no idea.

[westconn1]

pls use code tags when posting code

this should really be a SUB as it returns nothing to the caller

ActiveSheet.Hyperlinks.Add _
Anchor:=Cells(intCtr, 3), _
Address:="", _
SubAddress:="'" & Replace(ws.Name, "'", "''", 1) & "'!A1", _
TextToDisplay:=ws.Name

your problem is not using fully qualified ranges for all range object parameters, avoid using activesheet, specify the correct sheet for the target hyperlinks and specify the sheet for the range parameters of anchor
maybe like

wsMain.Hyperlinks.Add _
Anchor:=ws.Cells(intCtr, 3), _
Address:="", _
SubAddress:="'" & Replace(ws.Name, "'", "''", 1) & "'!A1", _
TextToDisplay:=ws.Name

after staring at the subaddress for a while i am still at a loss as to what you are replacing in the worksheet name
edit i can see this after posting the code within the tags