Copy / Paste Values & Formatting of Worksheet with Pivot Table


Board Regular
May 20, 2004
Hi there.

I generate reports for a client each day. They have gone through many iterations, the most recent of which utilizes a PivotTable. The PivotTable exists on its own worksheet (Tab). When my report is complete, I need to copy/paste values and formatting of the entire tab to a new worksheet so as to strip the formulas, etc. To be clear, I do not want a PivotTable in my new file, just the values and formulas of the table and worksheet (which has headers and footers).

I had been using the following VBA macro to accomplish that:

Sub PasteSheetsValues()

Dim w As Worksheet
For Each w In ActiveWorkbook.Sheets
With w.UsedRange
.Value = .Value
End With
Next w
End Sub

Unfortunately, now that my worksheet contains a PivotTable, this macro does not work. I receive a Run-time error 1004 ("You cannot move a part of a PivotTable report...).

Complicating matters is my near-complete ignorance of VBA.

Any suggestions?



Board Regular
Oct 14, 2010
Why can't you manually select the information you want copied and move it to a new sheet using paste special/values?


Board Regular
May 20, 2004
Thanks for the response.

I'm preparing a report for a client. As such, my source worksheet is highly formatted, with the PivotTable formatted for text, numbers, alternate row shading, column headers, etc. My worksheet also includes headers and footers with company logos. A straight paste / copy would lose all of that formatting.

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...