Copy / Paste Values & Formatting of Worksheet with Pivot Table

[lneidorf]

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
ActiveWindow.SelectedSheets.Copy
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?

Thanks!

 

[Ody]

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

 

[lneidorf]

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.