MS Excel VBA - Create Hyperlink to Worksheets based on value in row

[BxWill]

Timely insight needed!

I have a MS Excel workbook with more than 40 worksheets.

The worksheet names correspond to the account numbers in column C on Sheet 1 beginning at row 10.

Note, column C contains the formula "=MID(Trim(D10),1,6)

For Example, In column C at row 10 are the following account numbers;
40425
40426
40427
41425
41426
.
.
.

Objective

Create a hyperlink in column C to link to the corresponding worksheet based on the value in column C and at cell A1 of the account worksheets, create a hyperlink back to the main worksheet, the Analysis worksheet.

Each and every month, I overlay the data in columns D through L and would like to automatically generate the hyperlinks to facilitate the review of the accounts.

Using the VBA below, the code runs but no hyperlinks are created.

Troubleshooting for over 1 hour but would appreciate some insight/assistance.

Sub Hyperlink_ColumnC()
    Dim x As Integer
    Dim myLink As Hyperlink
    Dim strSubAddress As String
    'Dim xG, xOriginal As String
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim i As Integer
    Dim current As Worksheet
    
    Set wb = ActiveWorkbook
   For i = 1 To wb.Worksheets.Count
    Set current = wb.Worksheets(i)
    'For Each ws In ActiveWorkbook.Worksheets
       'If Cells(1, "S") <> "X" Then GoTo mycell     'Put an 'X' without the quotes in cell A1 of all worksheets to have hyperlinks created
       If Cells(1, 1) <> "X" Then GoTo mycell     'Put an 'X' without the quotes in cell A1 of all worksheets to have hyperlinks created
       'If current.Tab.ColorIndex = -4142 Then GoTo mycell
       'x = 36
        x = 8
       Do Until current.Cells(x, "C").Value = ""
           current.Select
           current.Cells(x, "C").Activate
           'xG = ActiveCell.Text                     'Not used
           'xOriginal = ActiveCell.Value     'Not used
     'The following makes the displayed value in column A a hyperlink
              With current
                  '.Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:="'" & ActiveCell.Value & "'!C8"
                  .Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:="'" & ActiveCell.Value & "'!A1"
              End With
 
           x = x + 1
       Loop
mycell:
   'Next ws
   Next i
End Sub

 

[mintjulep]

No hyperlinks are created."

.Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:="'" & ActiveCell.Value & "'!A1"

I guess because the Address value is nothing. And also possibly because the .value of the active cell that you are trying to make the hyperlink in is also nothing.

Anyway, you don't need to (and really should not) be messing around with select and activate. Just do what you want to do to the range that you want to do it to.

Follow this example.

 

[Andrzejek]

>In column C at row 10 are the following account numbers;
40425
40426
40427
41425
41426

So, I assume there is nothing in column C rows 1 to 9, and cell [blue]C8[/blue] is empty.
If so, your [tt]Do Until[/tt] is True from the very beginning and you never enter this loop:

x = [blue]8[/blue]
Do Until current.[blue]Cells(x, "C").Value = ""[/blue]

---- Andy

There is a great need for a sarcasm font.