运行时错误"1004":Microsoft Excel无法粘贴数据 [英] Run-time error '1004': Microsoft Excel cannot paste the data

问题描述

我查看了这个问题，并看到了一些解决方案，这些方案可以解决选择"或具有受保护的工作表等问题，但这些解决方案都不适用于我.

I have looked up the question and have seen several solutions addressing things like Select or having protected worksheets, none of which apply to me here.

由于各种原因，我无法发布整个代码，但是我将对其进行描述并发布导致问题的确切子代码.

For various reasons, I can't post the entire code, but I will give a description of what it does and post the exact sub that is giving me issues.

我有一个宏，它根据用户输入的月份和年份生成许多工作表(例如"1"-"31"或"1"-"30"等).若要生成这些工作表，宏将复制一个工作表的副本，命名为"EXAMPLE".复制的一件事是一张图片(只是上面带有单词"Export"的矩形)，上面附有一个宏.

I have a Macro that generates a number of worksheets based on the Month and Year input by the user (so "1" - "31" or "1" - "30" etc). To generate these worksheets, the macro makes copies of a worksheet fittingly named "EXAMPLE". One thing that is copied is a picture (just a rectangle with the word 'Export' on it) that has a macro attached to it.

我最近通过移动此图片的位置进行了我认为是外观上的更改，此后，当我运行宏时出现错误:

I recently made what I thought was a cosmetic change by moving the location of this picture, since then, when I run the macro I get an error:

运行时错误'1004':Microsoft Excel无法粘贴数据."

"Run-time error '1004': Microsoft Excel cannot paste the data."

以及结束"，调试"和帮助"的选项

And options for 'End' 'Debug' and 'Help'

如果我选择调试"，它将指向第二个宏，该宏在生成宏的过程中被调用.

If I select 'Debug' it points me to a second macro which is called during the process of the generation macro'

Sub CopyAllShapes()
Dim ws As Worksheet

' Sets the non-generated worksheets as an array
nSheets = Array("EXAMPLE", "Weekly Totals", "Menu")

' Copies the Picture from the EXAMPLE sheet to all worksheets not in the array and then assigns a 
' seperate Macro called "Export" to the picture on each of these sheets.
For Each ws In ActiveWorkbook.Worksheets
    If Not IsNumeric(Application.Match(ws.Name, nSheets,0)) Then
        Sheets("EXAMPLE").Shapes("Picture 1").Copy
        ws.Range("J62").PasteSpecial
        ws.Shapes("Picture 1").OnAction = "Export"
    End If
Next ws

Application.CutCopyMode = xlCopy
End Sub

调试"选项突出显示该行

The Debug option highlights the line

ws.Range("J62").PasteSpecial

真正让我困惑的是，如果我选择结束"而不是调试"，宏就会停止，但是所有工作表都粘贴了图片以及分配了导出宏"，并且一切正常.如果我是唯一使用此工具的人，那将是一个小麻烦，但是许多人使用此文档，无法可靠地告诉他们只是忽略"该错误.由于宏可以按预期运行，因此我该如何解决引起问题的原因并使错误消失?

What really confuses me is that if I select 'End' instead of 'Debug', the macro stops, but all the the sheets have had the picture pasted as well as the Export Macro assigned and everything works as expected. If I were the only person using this, it would be a minor annoyance, but this document is used by many people that can't reliable be told to "just ignore" the error. Since the macro is functioning as expected, how can i troubleshoot what is causing the problem and make the error go away?

正如我所说，我不能发布整个宏，但是如果有人需要更多信息，我可以发布一些零碎的东西.

As I said, I can't post the entire macro, but I can post some bits and pieces if anyone needs more info.

推荐答案

在迁移到Office 365和Win10时(不能说是哪一个是罪魁祸首)，我发现了一堆现有的宏，当它们出现时会给出相同的错误.尝试将复制的图像粘贴到工作表上.

On moving to Office 365 and Win10 (can't say which of those was the culprit) I found a bunch of existing macros which would give that same error when trying to paste a copied image onto a worksheet.

进入调试时，粘贴"行将突出显示，但是如果我单击继续"，它将(在一两次尝试后)运行且没有错误.

When entering debug, the "paste" line would be highlighted, but if I hit "Continue" it would (after one or two attempts) run with no errors.

我最终这样做:

'paste problem fix
Sub PastePicRetry(rng As Range)
    Dim i As Long
    Do While i < 20
        On Error Resume Next
        rng.PasteSpecial
        If Err.Number <> 0 Then
            Debug.Print "Paste failed", i
            DoEvents
            i = i + 1
        Else
            Exit Do
        End If
        On Error GoTo 0
        i = i + 1
    Loop
End Sub

...看似矫kill过正，但却是解决该问题的唯一可靠方法.

...which looks like overkill but was the only reliable fix for the problem.

清理并重构为一个独立的子视图.

cleaned up and refactored into a standalone sub.

这篇关于运行时错误"1004":Microsoft Excel无法粘贴数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！