Activating worksheet based on hyperlink address

[Ezmerelda]

Hi,

I have spreadsheet which contains a column of client numbers. these are hyperlinks to worksheets on another workbook which has pivot tables with the names of these clients each on its own worksheet. I'm trying to write a macro which will count the number of clients in the list and update the number in the client column (the hyperlink display name). I can't seem to use the worksheet reference in the hyperlink to activate the correct worksheet. It just opens the workbook. Is there a way to do this?

So far I have:

Sub updateClientNumbers()

Dim countClients As Integer
Dim sheetname As String
Dim lnkH As Hyperlink
Dim wkb As Workbook
Dim pt As PivotTable
Dim sht As Worksheet

For Each lnkH In ActiveSheet.Hyperlinks

Set wkb = Workbooks.Open(lnkH.Address)
Set pt = ActiveSheet.PivotTables(1)

countClients = pt.RowRange.Cells.count - 2
lnkH.TextToDisplay = countClients

wkb.Close

Next

End Sub

Apologies for my very basic VBA, I may be trying to do it completely the wrong way. Any help would be greatly appreciated.

 

[Ezmerelda]

I managed to solve it by stripping out the worksheet name from the subaddress and removing the quotation marks. I'm not sure this is the most efficient way of doing it but it works.

Sub updateClientNumbers()

Dim countClients As Integer
Dim sheetname As String
Dim sheet As Variant
Dim lnkH As Hyperlink
Dim wkb As Workbook
Dim pt As PivotTable
Dim sht As Worksheet
Dim Clientsheet As String

For Each lnkH In ActiveSheet.Hyperlinks

sheetname = lnkH.SubAddress
sheet = Split(sheetname, "!", 2)
Clientsheet = sheet(0)

Clientsheet = Replace(Clientsheet, "'", "", 1, 1)
If (Mid(Clientsheet, Len(Clientsheet), 1) = "'") Then
Clientsheet = Mid(Clientsheet, 1, Len(Clientsheet) - 1)
End If

Set wkb = Workbooks.Open(lnkH.Address)
Set sht = Worksheets(Clientsheet)
Set pt = sht.PivotTables(1)
countClients = pt.RowRange.Cells.count - 2
wkb.Close

lnkH.TextToDisplay = countClients

Next lnkH

End Sub