Macro runs, reverts to previous worksheet

[bruce2444]

I have a workbook which has 9 separate worksheets incorporated in to it. The problem I seem to be encountering is that when I run a macro which is designated for example Sheet4(Payment3), the macro runs fine but then it automatically reverts back to Sheet2(Payment1). The same thing happens no matter which worksheet I'm on...it always reverts back to Sheet2.

Here is the code that is being activated to run the macro

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    If Target.Range.Address = Sheet4.Range("C8").Address Then Sheet4.Worksheet_Hide_Rows
    If Target.Range.Address = Sheet4.Range("C9").Address Then Sheet4.Worksheet_Unhide_Rows

End Sub

Sub Worksheet_Hide_Rows()
    Dim i As Long
        With Worksheets("Payment3")
        For i = 11 To .Cells(.Rows.Count, "C").End(xlUp).Row
            .Rows(i).Hidden = .Cells(i, "F").Value = 0
        Next i
    End With
End Sub

Sub Worksheet_Unhide_Rows()
    Worksheets("Payment3").Rows.Hidden = False
End Sub

The code is the same as above on all of the worksheets with corresponding sheet references except Sheet1 which is completely different.

Anyone have any idea why it keeps doing this or what I need to correct this?

[pike]

Hi Bruce

Do you hide sheet1?

[bruce2444]

Yes, Sheet 1 would generate the first payment and then as the client bring in more receipts, we'd use subsequent sheets. So when you'd use sheet 2,..3, etc, sheet1 would have already hidden the unused rows

[shg]

Are all the hyperlinks on a given sheet to cells on the same sheet?

The code would be less confusing (and identical among sheets) is you used Me rather than sheet names or codenames:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    If Target.Range.Address = "C8" Then
        Me.Worksheet_Hide_Rows
    ElseIf Target.Range.Address = "C9" Then
        Me.Rows.Hidden = False
    End If
End Sub

Sub Worksheet_Hide_Rows()
    Dim i           As Long
    
    With Me
        For i = 11 To .Cells(.Rows.Count, "C").End(xlUp).Row
            .Rows(i).Hidden = .Cells(i, "F").Value = 0
        Next i
    End With
End Sub