vba 粘贴有时会抛出错误：Range 类的 Pastespecial 方法失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14704036/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Paste sometimes throws an error: Pastespecial method of Range class failed
提问by Ole Henrik Skogstr?m
I am trying to paste a manually copied sheet into a sheet named "Digital - Input".
我正在尝试将手动复制的工作表粘贴到名为“数字 - 输入”的工作表中。
I sometimes get the error
我有时会收到错误
Pastespecial method of Range class failed
Range 类的 Pastespecial 方法失败
This is my paste statement:
这是我的粘贴声明：
Worksheets("Digital - Input").range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
This is my complete code:
这是我的完整代码：
Sub pasteDigitalInput()
Application.ScreenUpdating = False
'open the source sheet
Call unlockAll
'show the input sheet
Call showerfunc("Digital - Input")
'paste values
'On Error GoTo feilmeld
Worksheets("Digital - Input").range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Update cell references
Call getTotals
'Set the start of ukestrykk for digital
showerfunc ("Kilder")
Sheets("Kilder").Select
range("J2").Select
Call findAnd("Netto spend pr uke:", "Kilder", "Digital - Input", 2, 0, , , True)
hiderfunc ("Kilder")
'Hide sheet
Call hiderfunc("Digital - Input")
Sheets("Digital").Select
Application.ScreenUpdating = True
'locks the sheet again
Call lockAll
Exit Sub
feilmeld:
'hiderfunc ("Digital - Input")
Sheets("Digital").Select
Call lockAll
Application.ScreenUpdating = True
MsgBox ("Du m? kopiere planen fra excel utskriften til Adform f?r du bruker denne knappen. OBS! kopier planen p? nytt og l?s opp Digital arket f?r du pr?ver igjen.")
End Sub
Edit:It seems to work right after opening the two workbooks. However if I use any other macro especially the macro that cleans the input sheet, I get the error.
编辑：打开两个工作簿后似乎可以正常工作。但是，如果我使用任何其他宏，尤其是清理输入表的宏，则会出现错误。
Clean input sheet macro:
清洁输入表宏：
Sub clearInputDigital()
Call ClearInput("Digital - Input", "Digital")
End Sub
Sub ClearInput(inputsheet As String, sourceSheet As String)
Application.ScreenUpdating = False
'Show the sheet
showerfunc (inputsheet)
Sheets(inputsheet).Select
Cells.Select
Selection.ClearContents
'Hide the sheet
hiderfunc (inputsheet)
Sheets(sourceSheet).Select
Application.ScreenUpdating = True
End Sub
回答by whytheq
(obviously I'm unsure what is happening in the subs and functions that you are calling)
（显然，我不确定您正在调用的subs 和functions 中发生了什么）
I don't always trust the excel VBA compiler to reference the objects that I'm coding it to reference, so I always try to fully qualify my code like the following
我并不总是相信 excel VBA 编译器会引用我正在对其进行编码以引用的对象，因此我总是尝试完全限定我的代码，如下所示
Sub pasteDigitalInput()
Excel.Application.ScreenUpdating = False
'open the source sheet
Call unlockAll
'show the input sheet
Call showerfunc("Digital - Input")
'paste values
'On Error GoTo feilmeld
Excel.ThisWorkbook.Worksheets("Digital - Input").Range("A1").PasteSpecial Paste:=Excel.xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'Update cell references
Call getTotals
'Set the start of ukestrykk for digital
showerfunc ("Kilder")
With Excel.ThisWorkbook.Sheets("Kilder")
.Activate
.Range("J2").Select
End With
Call findAnd("Netto spend pr uke:", "Kilder", "Digital - Input", 2, 0, , , True)
hiderfunc ("Kilder")
'Hide sheet
Call hiderfunc("Digital - Input")
Excel.ThisWorkbook.Sheets("Digital").Activate
Excel.Application.ScreenUpdating = True
'locks the sheet again
Call lockAll
Exit Sub
feilmeld:
'hiderfunc ("Digital - Input")
Excel.ThisWorkbook.Sheets("Digital").Activate
Call lockAll
Excel.Application.ScreenUpdating = True
MsgBox ("Du m? kopiere planen fra excel utskriften til Adform f?r du bruker denne knappen. OBS! kopier planen p? nytt og l?s opp Digital arket f?r du pr?ver igjen.")
End Sub
If you're only moving values maybe you could try avoiding using the clipboard altogether with a structure more like this :
如果您只是移动值，也许您可以尝试避免使用具有更像这样的结构的剪贴板：
Sub WithoutPastespecial()
'WORKING EAXAMPLE
Dim firstRange As Excel.Range
Dim secondRange As Excel.Range
Set firstRange = Excel.ThisWorkbook.Worksheets("Cut Sheet").Range("S4:S2000")
With Excel.ThisWorkbook.Worksheets("Cutsheets")
Set secondRange = .Range("A" & .Rows.Count).End(Excel.xlUp).Offset(1)
End With
With firstRange
Set secondRange = secondRange.Resize(.Rows.Count, .Columns.Count)
End With
secondRange.Value = firstRange.Value
End Sub
回答by Bharath Raja
Generally this error shows up when the current window(sheet) that is open is not the same one in which you are pasting something. Excel is very strict about it. When you do it manually, the sheet is selected obviously before pasting, so you will never see the error.
通常，当当前打开的窗口（工作表）与您粘贴内容的窗口（工作表）不同时，会出现此错误。Excel对此非常严格。当您手动执行此操作时，在粘贴之前明显选择了工作表，因此您永远不会看到错误。
So before pasting into the worksheet "Digital - Input", just insert a line, above that to select the sheet that you would like to paste in and then paste. Also, I would recommend using 'Thisworkbook' if you are handling multiple files so that...you know...excel doesn't get confused which workbook you're referring to. So your code would be
因此，在粘贴到工作表“数字 - 输入”之前，只需在其上方插入一行以选择要粘贴的工作表，然后粘贴。另外，如果您要处理多个文件，我建议您使用“Thisworkbook”，这样……您知道……excel 不会混淆您指的是哪个工作簿。所以你的代码将是
Thisworkbook.Worksheets("Digital - Input").Select
Worksheets("Digital - Input").range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Hope this helps.
希望这可以帮助。
