How to hyperlink current sheet to last cell with data in it in a specific row on another sheet

[Samlise]

Hi

Hope you can help.

I currently have a macro that saves and renames a sheet using a unique name from cell data, but I would also like it to automatically add a hyperlink to this current sheet to the last cell that has data in it in column 'E' on a sheet called "Log".

' Creates a new sheet based on the last number sequentially created after transfer
   
    Sheets("Transfer").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = Sheets("Data Validation Lists").Range("AC2").Value
    
    With ActiveSheet
        .Name = Sheets("Data Validation Lists").Range("AC2").Value
        pdfName = .Name
        pdfFullPath = pdfPath & "\" & pdfName
' Create PDF
        .ExportAsFixedFormat Type:=xlTypePDF, FileName:=pdfFullPath
    End With

Cheers

 

[Yongle]

Assuming that there is a value in EVERY row (ie no blank cells) in column E ...
This formula links to the last used cell in column E in sheet Data Validation Lists
=HYPERLINK("#'Data Validation Lists'!E"&COUNTA('Data Validation Lists'!E:E)+1,"Friendly name")

do you need to use VBA ?

 

[Samlise]

Thanks for that Yongle

I thought VBA was the best way because I am creating a new sheet per data entry, and in the 'log' sheet where a list of all entries is there would be a hyperlink in each row to each of the created sheets.

 

[Yongle]

You said this
a hyperlink to this current sheet to the last cell that has data in it in column 'E' on a sheet called "Log"

which I interpreted as
"a hyperlink to this current sheet to the last cell that has data in it in column 'E'
on a sheet called "Log"

But I now think that you want
a hyperlink from a sheet called "Log" (in the last cell that has data in it in column 'E')
to the current sheet

This creates a link TO the active sheet
Code below places the hyperlink formula in the next row in column E in sheet "Log"

    Dim aLink As Range, linkTo As String
    Set aLink = Sheets("Log").Cells(Sheets("Log").Rows.Count, "E").End(xlUp).Offset(1)
    linkTo = ActiveSheet.Name
    aLink.Value = "=HYPERLINK(""#'" & linkTo & "'!A1" & """,""" & linkTo & """)"

Adapting that to your code ...

    Dim aLink As Range, linkTo As String
    Sheets("Transfer").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = Sheets("Data Validation Lists").Range("AC2").Value
    
    With ActiveSheet
        Set aLink = Sheets("Log").Cells(Sheets("Log").Rows.Count, "E").End(xlUp).Offset(1)
        linkTo = .Name
        aLink.Value = "=HYPERLINK(""#'" & linkTo & "'!A1" & """,""" & linkTo & """)"
        .Name = Sheets("Data Validation Lists").Range("AC2").Value
        pdfName = .Name
        pdfFullPath = pdfPath & "\" & pdfName
' Create PDF
        .ExportAsFixedFormat Type:=xlTypePDF, FileName:=pdfFullPath
    End With

 

[Yongle]

LATE EDIT- deleted the duplicated renaming of the sheet in your original code

If you prefer not to use =HYPERLINK formula

This creates a link TO the active sheet
Code below places the hyperlink formula in the next row in column E in sheet "Log"

    Dim aLink As Range, linkTo As String
    Set aLink = Sheets("Log").Cells(Sheets("Log").Rows.Count, "E").End(xlUp).Offset(1)
    linkTo = ActiveSheet.Name
    aLink.Parent.Hyperlinks.Add aLink, Address:="", SubAddress:="'" & ActiveSheet.Name & "'!A1", TextToDisplay:=ActiveSheet.Name

Adapting that to your code ...

    Dim aLink As Range, linkTo As String
    Sheets("Transfer").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = Sheets("Data Validation Lists").Range("AC2").Value
 
    With ActiveSheet
         .Name = Sheets("Data Validation Lists").Range("AC2").Value
        Set aLink = Sheets("Log").Cells(Sheets("Log").Rows.Count, "E").End(xlUp).Offset(1)
        linkTo = .Name
        aLink.Parent.Hyperlinks.Add aLink, Address:="", SubAddress:="'" & .Name & "'!A1", TextToDisplay:=.Name   
        pdfName = .Name
        pdfFullPath = pdfPath & "\" & pdfName
' Create PDF
        .ExportAsFixedFormat Type:=xlTypePDF, FileName:=pdfFullPath
    End With

 

[Yongle]

EDIT - CORRECTION TO POST#4
- just spotted your code re-names the sheet TWICE
- moved 2nd incidence ABOVE my inserted lines

   Dim aLink As Range, linkTo As String
    Sheets("Transfer").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = Sheets("Data Validation Lists").Range("AC2").Value
   
    With ActiveSheet
         .Name = Sheets("Data Validation Lists").Range("AC2").Value
        Set aLink = Sheets("Log").Cells(Sheets("Log").Rows.Count, "E").End(xlUp).Offset(1)
        linkTo = .Name
        aLink.Value = "=HYPERLINK(""#'" & linkTo & "'!A1" & """,""" & linkTo & """)"
        .Name = Sheets("Data Validation Lists").Range("AC2").Value
        pdfName = .Name
        pdfFullPath = pdfPath & "\" & pdfName
' Create PDF
        .ExportAsFixedFormat Type:=xlTypePDF, FileName:=pdfFullPath
    End With

 

[Samlise]

Thank you Yongle

That worked brilliantly

Sorry my description wasn't very accurate!

Cheers

 

[Samlise]

I know I might be pushing it now but how can I make the cells hyperlink text bigger (16) using that VBA?

 

[Yongle]

does this do what you want

    aLink.Font.Size = 16

To learn how to do this yourself, try using the macro recoder
- amend cell font size, make font bold, italic etc
- look at what has been recoded and adapt it.

 

[Samlise]

Thanks again, I really appreciate your help.

I have been doing most of my VBA that way along with searching but sometimes I just can't get things to work together properly

What I tried for the size change didn't work and was nothing like as elegant as your code!!!