VBA to paste link to another spreadsheet

[mickyh]

Good Morning,

I would like some help with the following code (which I have adapted from another post) this copies cells from specified sheet and pastes to next available line in another workbook. it works perfectly when I try to paste values but I need it to paste a link.
any help gratefully appreciated.

Many Thanks Mick

Sub CopynPasteWrkBk()
Dim InputFile As Workbook
Dim OutputFile As Workbook
Dim Inputpath As String
Dim Outputpath As String '




'## Open both workbooks first:
Set InputFile = ActiveWorkbook
Set OutputFile = Workbooks.Open("C:\Users\MICK\Dropbox\Sandymount Construction\Estimates 2016\Estimate List.xlsx")




'Now, copy what you want from InputFile:
InputFile.Sheets("Imput Sheet").Activate
InputFile.Sheets("Imput Sheet").Range("B24:G24").Copy




'Now, paste to OutputFile worksheet:
OutputFile.Sheets("Estimates").Activate
OutputFile.Sheets("Estimates").Range("A" & Rows.Count).End(xlUp).Offset(1).Paste Link:=True




'Close InputFile & OutputFile:
OutputFile.Close savechanges:=True
Application.CutCopyMode = False


MsgBox "Data Successfully Logged"




End Sub

 

[ww4612]

you can sign a hyperline to the cell after pasted the value. you can use this code

ActiveSheet.Hyperlinks.Add Anchor:=OutputFile.Sheets("Estimates").Range("A" & Rows.Count).End(xlUp).Offset(1), Address:=Sheets("Estimates").Range("A" & Rows.Count).End(xlUp).Offset(1).value

 

[mickyh]

Thanks for the speedy reply,
I'm getting links added but not to the data I've copied. Do I need to add something to reference the specific cells and sheet?

Cheers
Mick

Sub CopynPasteWrkBk()
Dim InputFile As Workbook
Dim OutputFile As Workbook
Dim Inputpath As String
Dim Outputpath As String '




'## Open both workbooks first:
Set InputFile = ActiveWorkbook
Set OutputFile = Workbooks.Open("C:\Users\MICK\Dropbox\Sandymount Construction\Estimates 2016\Estimate List.xlsx")




'Now, copy what you want from InputFile:
InputFile.Sheets("Imput Sheet").Activate
InputFile.Sheets("Imput Sheet").Range("B24:G24").Copy




'Now, paste to OutputFile worksheet:
OutputFile.Sheets("Estimates").Activate
OutputFile.Sheets("Estimates").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteAll
ActiveSheet.Hyperlinks.Add Anchor:=OutputFile.Sheets("Estimates").Range("A" & Rows.Count).End(xlUp).Offset(1), Address:=Sheets("Estimates").Range("A" & Rows.Count).End(xlUp).Offset(1).Value


'Close InputFile & OutputFile:
OutputFile.Close savechanges:=True
Application.CutCopyMode = False


MsgBox "Data Successfully Logged"




End Sub

 

[mickyh]

Thanks I have found a solution
Regards
Mick

Sub CopynPasteWrkBk()
Dim InputFile As Workbook
Dim OutputFile As Workbook
Dim Inputpath As String
Dim Outputpath As String '




'## Open both workbooks first:
Set InputFile = ActiveWorkbook
Set OutputFile = Workbooks.Open("C:\Users\MICK\Dropbox\Sandymount Construction\Estimates 2016\Estimate List.xlsx")




'Now, copy what you want from InputFile:
InputFile.Sheets("Imput Sheet").Activate
InputFile.Sheets("Imput Sheet").Range("B24:G24").Copy




'Now, paste to OutputFile worksheet:
OutputFile.Sheets("Estimates").Activate
OutputFile.Sheets("Estimates").Range("A" & Rows.Count).End(xlUp).Offset(1).Select
ActiveSheet.Paste link:=True




'Close InputFile & OutputFile:
OutputFile.Close savechanges:=True


Application.CutCopyMode = False




MsgBox "Data Successfully Logged"




End Sub