Creating Hyperlink to the last worksheet in the workbook (VBA)

[kps1201]

Hi All,

I'm trying to write a VBA formula that would insert a hyperlink to the last tab of the workbook. Currently I'm able to get the hyperlink displayed where I want it with the text that I want it. Bust using a location of "Sheets(Sheets.Count)" within the formula isn't getting me anywhere.

Thanks,

 

[Gingertrees]

try this:

Sub AddHyperlink()
    Dim x As Integer
    x = ThisWorkbook.Sheets.Count
    ActiveSheet.Hyperlinks.Add Sheets("NameofSheet").Cells(20, 1), "", Sheets(x).Name & "!A1", "", "Hello"
'-------this places a hyperlink called "Hello" in cell A20 on Sheet "NameofSheet".
'-------the hyperlink goes to the last sheet in the workbook, cell A1
End Sub

 

[kps1201]

Hey - I got it to work thanks to your help.

Appreciate it!

 

[kps1201]

Perhaps you can help me with another similar problem? I'm trying to perform a vlookup function within VBA, which isn't difficult in itself, but the problem lies within the location of the data range.

A new tab is created as part of this macro I'm building, which is why we we're focusing on the last tab in the workbook in the above problem. However, the name of this worksheet is always going to be changing as more and more are added as part of the macro. I'm trying to get a vlookup from the recently created last tab into my summary page. Right now I have the VBA as:

Sheets("Summary").Select
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(0,2).Select

ActiveCell.FormulaR1C1 = _
'"=VLOOKUP(""Overall Status"", LastWorksheet[-2]:C[3], 6, FALSE)"

The value of the LastWorksheetneeds to be the last worksheet in the workbook. The location of the data we're pulling will always been the same.

Any thoughts?

Really appreciate it.

 

[Gingertrees]

I can tell you how to do it in VBA, but writing the "=VLOOKUP("... in VBA is a serious pain in the butt. I've been trying to get the god**** syntax right for too long, and I'm throwing in the towel.

Here's how you can do it in VBA, though - downside being, this won't recalculate when your worksheet changes. You'd have to make a worksheet_change event with this, to get it to work:

Sub vlooklast
    Sheets("Summary").Select
    Dim x As Integer
    x = ThisWorkbook.Sheets.Count
    Dim R As Range
    Set R = Sheets(x).Range("A1:F19") '<--------you said the location is fixed, right? Modify this to match it.
    ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(0,2).Select
    Selection.Value = Application.WorksheetFunction.VLookup("Overall Status", R, 6, False)
End Sub

 

[kps1201]

That definitely got me closer, but just as you mentioned, it doesn't recalculate when the worksheet changes which is the functionality i'm trying to incorporate. Would it be possible by setting the target cell equal to that cells value and not using a vlookup? Something as easy as the "=E6" formula or something, so the value of the target cell would change when the value was modified in the worksheet?

 

[Gingertrees]

That's the thing - I can't figure out how to get it to refer to Sheet x range(something) when x is variable. I can do it VB, but not in workbook. Maybe start a separate thread asking about this?