VBA to paste link to another spreadsheet

mickyh

New Member
Joined
Jul 10, 2012
Messages
12
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
Code:
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

ww4612

Well-known Member
Joined
Apr 24, 2014
Messages
515
you can sign a hyperline to the cell after pasted the value. you can use this code
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
 
Last edited:

mickyh

New Member
Joined
Jul 10, 2012
Messages
12
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

Code:
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

New Member
Joined
Jul 10, 2012
Messages
12
Thanks I have found a solution
Regards
Mick

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,535
Messages
5,602,217
Members
414,513
Latest member
junbuggle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top