Copy / Paste Values & Formatting of Worksheet with Pivot Table

lneidorf

Board Regular
Joined
May 20, 2004
Messages
82
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!
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Ody

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

lneidorf

Board Regular
Joined
May 20, 2004
Messages
82
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,661
Messages
5,573,475
Members
412,531
Latest member
gkrishna788
Top