Shape Hyperlink Screentip macro

Hello,

I am currently trying to make a macro that will update a shapes hyperlink screentip. I am using a range that has my data and shape names. Please let me know what is the matter with the current macro I have been trying to develop.

I have attached my excel file, which contains only the bare essential information that you would need to understand what my goal is. the hyperlinks have already been created to go to sheets that I have deleted because of sensitive data.


Sub RPMStates()
'
'
'
Dim cstate As Integer
Dim StateName As String
Dim StateValue As Integer
Dim rngstates As Range

Set rngstates = Range(ThisWorkbook.Names("rpm").RefersTo)

With Worksheets("map")
For cstate = 1 To rngstates.Rows.Count
StateName = rngstates.Cells(intState, 1).Text
StateValue = rngstates.Cells(intState, 5).Value

Shape.Range(Array(StateName)).Select
Selection.ScreenTip = StateValue

Next
End With

End Sub


Thank you for your time,
Andy

I really could use your expert opinions on this one.

Thanks

Sub RPMStates()

    Dim intState As Integer
    Dim strStateName As String
    Dim intStateValue As Single
    Dim rngstates As Range
    
    Set rngstates = ThisWorkbook.Worksheets("Summary by Region").Range("rpm")
    
    With Worksheets("map")
        For intState = 1 To rngstates.Rows.Count
            strStateName = rngstates.Cells(intState, 1).Text
            intStateValue = rngstates.Cells(intState, 5).Value
           .Hyperlinks.Add Anchor:=.Shapes(strStateName), Address:="", ScreenTip:=CStr(intStateValue)
        Next intState
    End With

End Sub

How would I code the above hyperlinks.add line to make the hyperlink go to a worksheet in the workbook I am working with?

Thanks