Forum Discussion
Pasting Pivot Table as Values... losing Borders and formatting
Hi Brian,
There is a fairly good technique to paste the PivotTable values and formats, but it cost you some extra steps!
You can use the Format Painter to grab the format to the second instance of the PivotTable.
After you paste the PivotTable as values, go to the original PivotTable, highlight it, press Format Painter button, and then paint the second PivotTable!
Another method (Two levels Paste):
- Highlight the first PivotTable and copy it.
- Go to another location, and press Ctrl+Alt+V to open the Paste Special dialog box.
- Select Values and then hit OK.
- Press Ctrl+Alt+V again.
- Select Formats and then hit OK again!
Hey Stranger :-) This only recently started happening to me also. Just "upgraded" to 365 at work, and this started happening. Was on 2013 prior to that and no issues. I have a little routine attached to a a key combo that hardwires whatever I have selected, and I do this a lot with Pivot tables. Been a major PITA for me.
Tried Debs' workaround from John W, of using the Office clipboard and found that I get different results if I do or don't have the Filters selected when i do the copy instead of just the table of values. Just strange.
That aside, I have to believe this was an unintended consequence of some other change in the code by the Devs, as surely no-one in their right mind would simply assume that this was a logical thing to do?
That aside, hope you are all well.
Regards
Ken.....................
Nice to hear from you again.
What I find odd is that 2010 behaves the same as current 2016. Perhaps there is more involved than just a new version and this is related to some other update. Anyway, the work-around then is to copy the PT and paste it elsewhere. Painful, but it works.
Still frustrated with the change in behavior!
Did you know that Windows 7 and Office 365 does retain the pivot formatting when copying values-only in VBA. I migrated to Windows 10 and I too was amazed to find out this change.
What is everybody's best VBA approach for this?
There is a VBA code on the Contexture's site which I dismissed because I need to copy the entire sheet that includes a pivot. I ended up with an ugly solution:
- Copy and paste just the values into the destination worksheet
- Go back to the source; copy again; then paste the formatting only
