运行时错误1004工作表的粘贴方法失败（试图从剪贴板粘贴） [英] Run-time Error 1004 Paste Method of Worksheet Failed (Trying to Paste from Clipboard)
问题描述
（Excel VBA 2007）。我在宏生成的代码中收到错误 - Excel编写代码，为什么不运行？
某些背景：
在我的VBA应用程序，我正在尝试用格式化的数据透视表复制工作表，并将其粘贴到一个新的工作簿中，保持格式，而不是链接到源数据。一个简单的粘贴包括源数据。具有值，然后格式的粘贴特殊不会带来数据透视表格式。
我发现了一个帖子 http://blog.contextures.com/archives/2010/09/22/copy-pivot -table-format-and-values / 说明如何手动执行此操作 - 从剪贴板中粘贴。
我记录了一个宏，它生成了以下代码：
Sub PivotCopyPaste（）
'
'PivotCopyPaste宏
'
'目标：在第一张表上打开一个包含数据透视表报表的工作簿。
'创建一个新的工作簿并粘贴数据透视表，没有
'的数据源数据，但保留透明度格式
Workbooks.Open文件名：=\\MyServer \MyFolder\PivotReport.xls
Cells.Select
Selection.Copy
Workbooks.Add
Cells.Select
'我认为下面的一行强制粘贴从剪贴板
Application.CutCopyMode = False
ActiveSheet.Paste'这行上的错误
End Sub
当我运行这个'按原样'时，我得到一个错误：运行时错误1004：工作表类的粘贴方法失败在 ActiveSheet.Paste 行。
如果我拿出 Application.CutCopyMode = False 行，宏运行，但它粘贴在源数据中（即它仍然是活动数据透视表） - 不是我想要的。
我已经找到很多关于此错误的引用 - 包括 http://www.mrexcel.com/forum/excel-questions/387000-runtime-error-1004-a。 HTML 。
他们建议剪贴板可能是空的。我在Excel中可以看到剪贴板窗格，它显示的是某些东西。
他们建议对旧的和新的工作表/范围进行明确的引用，以便可以引用它们变量而不是依赖于正确的活动 - 我尝试了，并没有太大的区别（只是将错误消息的文本更改为_Worksheet失败的方法粘贴。 >
是否可以做我想做的事情？如果是这样，怎么办？所有的帮助都很感激。
{后续行动：在同一个博客上，Debra提供了一些代码来粘贴数据/格式的数据透视表：我无法粘贴链接 - 没有足够的声誉 - 但我已经包括在我的评论中的链接@Rory在下面。
这允许我单独粘贴每个数据透视表，但每个报表上还有其他元素，每次都不同，例如公司徽标（可选）包含数据透视表过滤器的隐藏行，标题我真的是在贴上所有的一切解决方案，使我的代码简单！ }
我没有做过很多测试，但尝试这样 - 它应该只是粘贴任何复制的东西，包括图片，但是将数据透视表留作格式化的静态范围：
私有声明函数OpenClipboard Libuser32.dll（ByVal hwnd As Long ）As Long
私有声明函数CloseClipboard Libuser32.dll（）As Long
私有声明函数EnumClipboardFormats Libuser32（ByVal wFormat As Long）As Long
私有声明函数GetClipboardFormatName Lib user32别名GetClipboardFormatNameA（_
ByVal wFormat As Long，ByVal lpString As String，_
ByVal nMaxCount As Long）As Long
私有声明函数SetClipboardData Libuser32 （ByVal wFormat As Long，ByVal hMem As Long）As Long
私有声明函数GetClipboardData Libuser32.dll（ByVal wFormat As Lon g）As Long
私有声明函数GlobalLock Libkernel32.dll（ByVal hMem As Long）As Long
私有声明函数GlobalUnlock Libkernel32.dll（ByVal hMem As Long）As Long
私有声明函数lstrlen Libkernel32.dll别名lstrlenA（_
ByVal lpString As Long）As Long
私有声明函数lstrcpy Libkernel32.dll（_
ByVal lpStr1 As Any，ByVal lpStr2 As Any）As Long
Sub PasteAsLocalFormula（）
'如果clipbaord包含Excel范围，任何公式都将被粘贴不变，移动工作表和_
对目的地工作簿的单元格引用。
Dim S As String
Dim i As Long，CF_Format As Long
Dim SaveDisplayAlerts As Boolean，SaveScreenUpdating As Boolean
Dim HTMLInClipBoard As Boolean
Dim Handle As Long，Ptr As Long，FileName As String
'枚举剪贴板格式
如果OpenClipboard（0）然后
CF_Format = EnumClipboardFormats（0&）
尽管CF_Format& ; 0
S = String（255，vbNullChar）
i = GetClipboardFormatName（CF_Format，S，255）
S = Left（S，i）
HTMLInClipBoard = InStr（1，S， HTML格式，vbTextCompare）> 0
如果HTMLInClipBoard然后
Handle = GetClipboardData（CF_Format）
Ptr = GlobalLock（Handle）
Application.CutCopyMode = False
S =空格$（ lstrlen（ByVal Ptr））
lstrcpy S，ByVal Ptr
GlobalUnlock Ptr
SetClipboardData CF_Format，Handle
ActiveSheet.PasteSpecial格式：=HTML
退出执行
End If
CF_Format = EnumClipboardFormats（CF_Format）
循环
CloseClipboard
如果
End Sub
(Excel VBA 2007). I'm getting an error in code generated by a macro - Excel wrote the code, so why won't it run?
Some background: Within my VBA application, I am trying to copy a worksheet with a formatted pivot table and paste it into a new workbook, keeping the formatting, but not links to the source data. A simple 'Paste' includes the source data. A 'Paste Special' with values and then formats doesn't bring across PivotTable formats.
I found a post http://blog.contextures.com/archives/2010/09/22/copy-pivot-table-format-and-values/ which explains how to manually do this - paste in from the Clipboard. This works when done manually.
I recorded a macro and it generated the following code:
Sub PivotCopyPaste()
'
' PivotCopyPaste Macro
'
' Aim: Open a workbook with a pivot table report on the first sheet.
' Create a new workbook and paste the pivot table in, without
' pivot source data, but keeping pivot formatting
Workbooks.Open Filename:="\\MyServer\MyFolder\PivotReport.xls"
Cells.Select
Selection.Copy
Workbooks.Add
Cells.Select
'I think the line below forces the paste from the Clipboard
Application.CutCopyMode = False
ActiveSheet.Paste 'ERRORS on this line
End Sub
When I run this 'as is', I get an error: "Run-Time Error 1004: Paste method of Worksheet class failed" on the ActiveSheet.Paste line.
If I take out the Application.CutCopyMode = False line, the macro runs, but it pastes in the Source Data (i.e. it's still an active PivotTable) - not what I want.
I have found lots of references to this error - including http://www.mrexcel.com/forum/excel-questions/387000-runtime-error-1004-a.html.
They suggest the clipboard may be empty. I have the clipboard pane visible in Excel and it shows something is there.
They suggest putting explicit references to the old and new sheets/ranges so that they can be referenced by variable rather than relying on the correct one being 'Active' - I tried that and it didn't make much difference (just changed the text of the error message to " Method 'Paste' of object '_Worksheet' failed".
Is it possible to do what I am trying to do? If so, how? All help gratefully received.
{Follow-up: on the same blog, Debra provides some code to paste in the data/formats for a pivot table: I can't paste the link here - not enough reputation yet - but I've included the link in my comment to @Rory below.
This allows me to paste each pivot table individually, but there are other elements on each report, different each time, such as company logo, (optionally) hidden rows containing the pivot table filters, Titles etc. I was really after a 'paste everything on the sheet' solution to make my code simple! }
I haven't done a lot of testing but try this - it should just paste whatever was copied, including pictures, but leaves pivot tables as a static range with formatting:
Private Declare Function OpenClipboard Lib "user32.dll" (ByVal hwnd As Long) As Long
Private Declare Function CloseClipboard Lib "user32.dll" () As Long
Private Declare Function EnumClipboardFormats Lib "user32" (ByVal wFormat As Long) As Long
Private Declare Function GetClipboardFormatName Lib "user32" Alias "GetClipboardFormatNameA" ( _
ByVal wFormat As Long, ByVal lpString As String, _
ByVal nMaxCount As Long) As Long
Private Declare Function SetClipboardData Lib "user32" (ByVal wFormat As Long, ByVal hMem As Long) As Long
Private Declare Function GetClipboardData Lib "user32.dll" (ByVal wFormat As Long) As Long
Private Declare Function GlobalLock Lib "kernel32.dll" (ByVal hMem As Long) As Long
Private Declare Function GlobalUnlock Lib "kernel32.dll" (ByVal hMem As Long) As Long
Private Declare Function lstrlen Lib "kernel32.dll" Alias "lstrlenA" ( _
ByVal lpString As Long) As Long
Private Declare Function lstrcpy Lib "kernel32.dll" ( _
ByVal lpStr1 As Any, ByVal lpStr2 As Any) As Long
Sub PasteAsLocalFormula()
'If the clipbaord contains an Excel range, any formula is pasted unchanged, moving sheet and _
cell references to the destination workbook.
Dim S As String
Dim i As Long, CF_Format As Long
Dim SaveDisplayAlerts As Boolean, SaveScreenUpdating As Boolean
Dim HTMLInClipBoard As Boolean
Dim Handle As Long, Ptr As Long, FileName As String
'Enumerate the clipboard formats
If OpenClipboard(0) Then
CF_Format = EnumClipboardFormats(0&)
Do While CF_Format <> 0
S = String(255, vbNullChar)
i = GetClipboardFormatName(CF_Format, S, 255)
S = Left(S, i)
HTMLInClipBoard = InStr(1, S, "HTML Format", vbTextCompare) > 0
If HTMLInClipBoard Then
Handle = GetClipboardData(CF_Format)
Ptr = GlobalLock(Handle)
Application.CutCopyMode = False
S = Space$(lstrlen(ByVal Ptr))
lstrcpy S, ByVal Ptr
GlobalUnlock Ptr
SetClipboardData CF_Format, Handle
ActiveSheet.PasteSpecial Format:="HTML"
Exit Do
End If
CF_Format = EnumClipboardFormats(CF_Format)
Loop
CloseClipboard
End If
End Sub
这篇关于运行时错误1004工作表的粘贴方法失败（试图从剪贴板粘贴）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
