2007 Vba - Copy cell as a link to another closed workbook
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....)
Dim wb As Workbook
Dim ws As Worksheet
Application.ScreenUpdating = False
Set wb = Workbooks.Open(FileName:=".....target.xls")
Set ws = wb.Worksheets(1)
nextrow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
ws.Cells(nextrow, "A").PasteSpecial Paste:=xlPasteValues
ws.Cells(nextrow, "B").ActiveSheet.Paste Link:=True
Set ws = Nothing
Set wb = Nothing
anysuggestions would be greatly appreciated !
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1