Hi all, hope someone can help
I'm trying to copy / create a link in a closed workbookand keep failing hard.
In a nutshell;
WorkBook = "source"
Sheet 1 - Cell B2 = "Apples"
then copy across to
WorkBook = "target"
Sheet 1 - Next Empy cell in col B = "=C:\Users\[source.xls]Sheet1!$B$3
I can get vba to do the copying, but only as values, so I tried to record a macro to see how it handles link pasting, but I can't make it work.
Here is my code...It's falling over at> ws.Cells(nextrow, "B").ActiveSheet.Paste Link:=True
(Soory, but not sure how to put the code in it's own box like other posters....)
Sub Transfer()
Dim wb As Workbook
Dim ws As Worksheet
Application.ScreenUpdating = False
Set wb = Workbooks.Open(FileName:=".....target.xls")
Set ws = wb.Worksheets(1)
With ws
nextrow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With
With Workbooks("source.xls").ActiveSheet
.Range("A2").Copy
ws.Cells(nextrow, "A").PasteSpecial Paste:=xlPasteValues
.Range("B2").Copy
ws.Cells(nextrow, "B").ActiveSheet.Paste Link:=True
End With
wb.Save
wb.Close
Set ws = Nothing
Set wb = Nothing
End Sub
anysuggestions would be greatly appreciated !
Bookmarks