title: Run-time Error 1004 Paste Method of Worksheet Failed (Trying to Paste from Clipboard)
body: <p>(Excel VBA 2007). I'm getting an error in code generated by a macro - Excel wrote the code, so why won't it run?</p>
  
  <p>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.</p>
  
  <p>I found a post <a href="http://blog.contextures.com/archives/2010/09/22/copy-pivot-table-format-and-values/" rel="nofollow">http://blog.contextures.com/archives/2010/09/22/copy-pivot-table-format-and-values/</a> which explains how to manually do this - paste in from the Clipboard.  This works when done manually.</p>
  
  <p>I recorded a macro and it generated the following code:</p>
  
  <pre><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
  </code></pre>
  
  <p>When I run this 'as is', I get an error:  "Run-Time Error 1004:  Paste method of Worksheet class failed" on the <strong>ActiveSheet.Paste</strong> line.  </p>
  
  <p>If I take out the <strong>Application.CutCopyMode = False</strong> line, the macro runs, but it pastes in the Source Data (i.e. it's still an active PivotTable) - not what I want.</p>
  
  <p>I have found lots of references to this error - including <a href="http://www.mrexcel.com/forum/excel-questions/387000-runtime-error-1004-a.html" rel="nofollow">http://www.mrexcel.com/forum/excel-questions/387000-runtime-error-1004-a.html</a>.    </p>
  
  <p>They suggest the clipboard may be empty.  I have the clipboard pane visible in Excel and it shows something is there.</p>
  
  <p>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".</p>
  
  <p>Is it possible to do what I am trying to do?  If so, how?  All help gratefully received.</p>
  
  <p>{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.</p>
  
  <p>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!  }</p>