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 !