VBA to paste link to another spreadsheet

mickyh

New Member
Joined
Jul 10, 2012
Messages
35
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

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
35
Office Version
  1. 365
Platform
  1. Windows
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
35
Office Version
  1. 365
Platform
  1. Windows
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,675
Messages
5,838,714
Members
430,566
Latest member
ChanchalSingh

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