Excel VBA pastelink run-time error 1004: Application-defined or object-defined error.

Question

I am trying to create a link from a cell on one sheet to a cell on the other sheet, but when I run it I get this runtime error:

Application-defined or object-defined error.

Here is the code:

Worksheets(sheetFrom).Cells(fromRow, fromCol).Copy
Worksheets(sheetTo).Cells(toRow, toCol).Select
ActiveSheet.Paste Link:=True

I am checking the to/from values and they are correct.

update seems like its the cell selection that's causing the problem.

update 2 When the from sheet is the same as to sheet, there is no problem. What am I missing?

Answer 1

You're getting that error because Worksheets(sheetTo) is not the active sheet when you're trying to paste to it.

If you add a line to your code

Worksheets(sheetFrom).Cells(fromRow, fromCol).Copy
Worksheets(sheetTo).Activate
Worksheets(sheetTo).Cells(toRow, toCol).Select
ActiveSheet.Paste Link:=True

it works.

Edit to respond to guitarthrower's answer:

Paste link, as far as I can tell, only works with Worksheet.Paste, not Range.Paste. Worksheet.Paste pastes the link into the active cell. Although I agree with you that Select should be avoided, using this type of code seems to require it.

Another alternative might be to create the formula instead of pasting a link:

Worksheets(sheetTo).Cells(toRow, toCol).Formula = _
    "=" & Worksheets(sheetFrom).Name & "!" & Worksheets(sheetFrom).Cells(fromRow, fromCol).Address

Answer 2

I try to stay away from using Select. It can introduce problems in the flow of the code and make things hard to debug later.

Why not try:

Worksheets(sheetFrom).Cells(fromRow, fromCol).Copy
Worksheets(sheetTo).Cells(toRow, toCol).Paste Link:=True