VBA to automatically hyperlink to VBA created sheet

[Samlise]

Hi everyone

I've done some searching but can't find what I need to get this to work....

Brief description - I have a submission sheet where I input data, certain data is copied cell by cell to a log sheet, the whole of the submission sheet is copied to a transfer sheet where a review section is underneath the copied part, then the whole transfer sheet is used to create a new sheet with a sequential name so I can locate it.


I have a VBA to copy all the data, then create and rename (sequentially) new sheets but at the same time I would like to create a hyperlink from the newly transferred 'Log' data to the newly created sheet

Sub Copy2()
'
' Copy2 Macro
'
' Keyboard Shortcut: Ctrl+w
'
    
  ' Macro to copy new data from XYZ form to the transfer
  ' sheet where part two of the form is located
    
    Worksheets("XYZ Form").Range("A1:S19").Copy _
    Destination:=Worksheets("XYZ Transfer").Range("A1")
      
  ' Copies key data to log sheet

    Sheets("XYZ Form").Range("O14").Copy
    Sheets("Log").Range("K" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

    Sheets("XYZ Form").Range("N4").Copy
    Sheets("Log").Range("M" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

  ' Creates a new sheet based on the last number sequentially created after transfer
   
    Sheets("XYZ Transfer").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = Sheets("Data Validation Lists").Range("AB2").Value

  ' Pastes a new empty form over the form that's just been transferred ready for next time

    Worksheets("Blank - DO NOT USE").Range("A1:S19").Copy _
    Destination:=Worksheets("XYZ Form").Range("A1")



    Sheets("Created").Select
End Sub

Any help would be fantastic

Carl

 

[davesexcel]

You can use the macro recorder for this, to see what the add hyperlink would look like

 

[Samlise]

Thanks Dave

the recorder has given me this...

Sub Hyperlink()
'
' Hyperlink Macro
'
' Keyboard Shortcut: Ctrl+h
'
    ActiveCell.Offset(-6, -3).Range("A1").Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "'PA-3-1'!A1"
End Sub

But I'm not clued up enough to know how to change it so that it hyperlinks using the last data in the column (E) and links to the sheet just created using the same name as the last data.

Any ideas?