Run-time Error 1004 Paste Method of Worksheet Failed (Trying to Paste from Clipboard)

0
votes

(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! }

2
vbaexcel
Do you want to do a pastespecial values? – Siddharth Rout
If you read the comments below that post, you'll see Debra has written some code to do what you want. – Rory
No, because that loses the formatting. And if I then use PasteSpecial Formats, it only brings across formats applied to individual cells, not the formats from the PivotTable Style. I also have an image (company logo) that I want to paste across - this doesn't seem to come with PasteSpecial. – jj_searcher
Thanks for the pointer, Rory, I am taking a look at Debra's page now - sorry I didn't spot that before... will get back to you when I've tested it out. – jj_searcher
Hi @Rory thank you for the pointer - Debra's solution allows me to paste the values and Pivot formats for each pivot table individually. This is progress. However I have other elements on the page (different for each report) and sometimes users would like to hide the rows showing the filters etc.... so the code will get more complicated. I was after a 'paste all' solution, but maybe there's not such thing! I am going to park problem for now.. am new to posting - do I leave this 'as is' or mark 'answered'? – jj_searcher

2 Answers

3
votes

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
1
votes

Adding this here as it's the first StackOverflow link served up by Google for the error "Paste method of worksheet class failed".

It seems that this error can occur when Excel isn't ready to paste. I had the error occur sporadically when copying one of a set of logo images by VBA from a hidden sheet into the main sheet. In the end I found that my code seems a lot more robust after adding a
Do While Not Application.Ready: Sleep 10: Loop immediately before the .Copy and also between the subsequent .Select and .Paste. This required placing
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) up at the top of the module, of course. I also have a DoEvents after the .Copy (following an earlier suggestion I'd found somewhere), and that seems to help too. I haven't seen the error since, FWIW :)

UPDATE - I still had errors from time to time, so I resorted to error trapping along the lines of the following. The TryLogoAgain: label goes before the earlier .Copy (not shown) because it seems to be the .Copy that has failed to work, leaving the .Paste to fail (retrying just the .Paste over and over again never worked).

On Error Resume Next
Worksheets(1).Paste Destination:=Worksheets(1).Range("B1")
If Err.Number <> 0 Then Err.Clear: MsgBox "Excel is struggling to copy something, trying again...": Sleep (10): GoTo TryLogoAgain

So far it has always managed to work on the second try! (Excel 2010 btw)