Forum Discussion

Copper Contributor

Feb 16, 2018

Solved

Pasting Pivot Table as Values... losing Borders and formatting

This just started within the last 30 days or so... it appears that when pasting a Pivot Table as values over itself... borders and formatting are now eliminated. Extremely frustrating as I have the ...

excel

Formulas and Functions

PivotTable

Haytham Amairah
Feb 16, 2018
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!

Haytham Amairah

Silver Contributor

Feb 16, 2018

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!

Brian Catalano

Copper Contributor

Feb 20, 2018

Thanks so much for your suggestions Haytham! I've modified my code to do just as you recommended (the 2nd method). Sad to see this one change though.... need to change my habits (maybe even just write a new macro) for hard-coding a pivot table!

JKPieterse
Silver Contributor
Feb 20, 2018
I've had a look at various Excel versions I have at my disposal.

Excel 2003: Format is retained

Excel 2010: Format is lost

Excel 2013: Format is lost

Excel 2016 Monthly channel: Format is lost

Excel 2016 Insider fast: Format is lost
- Brian Catalano
  Copper Contributor
  Feb 21, 2018
  Haha, I'm not nuts! I can explain...
  
  Most of my core files were created with Excel 2003. Copying them and opening them in whatever recent-version of Excel is on my system (I work at 3M and our Office editions are updated constantly).
  
  So to test this, I got my old notebook out of my drawer.... opened my files and sure enough... I'm able to copy and paste-as-values OVER an existing PivotTable, and all formatting is retained as the PivotTable is overwritten with Values.
  
  Old Computer Excel Version... 1705 (Build 8201.2213) Deferred Channel
  
  New Computer Excel Version... 1708 (Build 8431.2153) Semi-Annual Channel
  
  So clearly the newest build handles formatting differently for old files... aligning them to how you say Excel has behaved since at least the 2010 version. I don't expect this behavior to change, so I'll code my way around it... that's what makes Excel so great.... VBA.
  
  Thanks ALL for your help,
  
  Brian Catalano
  - JKPieterse
    Silver Contributor
    Feb 21, 2018
    Thanks for clarifying the situation, that certainly explains things!