Excel VBA pastelink运行时错误1004:应用程序定义或对象定义的错误 [英] Excel VBA pastelink run-time error 1004: Application-defined or object-defined error

问题描述

我正在尝试创建从一张纸上的单元格到另一张纸上的单元格的链接，但是当我运行它时，出现此运行时错误:

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.

这是代码:

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

我正在检查to/from值，它们是正确的.

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

更新似乎是导致问题的单元格选择.

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

更新2 当从"工作表与到工作表"相同时，没有问题.我想念什么?

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

推荐答案

您会收到此错误，因为当您尝试粘贴到工作表时，工作表(sheetTo)不是活动工作表.

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

如果在代码中添加一行

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

有效.

编辑以回复吉他弹奏者的回答:

Edit to respond to guitarthrower's answer:

粘贴链接仅适用于Worksheet.Paste，不适用于Range.Paste.Worksheet.Paste将链接粘贴到活动单元格中.尽管我同意您的意见，但应该避免使用Select，但是使用此类代码似乎是必需的.

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

这篇关于Excel VBA pastelink运行时错误1004:应用程序定义或对象定义的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！