Feb 16 2018 10:17 AM
Feb 16 2018 10:17 AM
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 need to distribute Pivot Table data to many corporate users, but do not want all of the underlying data to be exposed or shared.
Microsoft Office 365 ProPlus, Excel version 1708 (Build 8431.2153 Click-to run)
Is there any way to turn this off, or what is the work-around? Tried all kinds of various "pasting formats" and such.
Feb 16 2018 10:59 AM
Feb 16 2018 11:39 AM
You can share a Pivot Table with the formatting without the underlying data.
In the Pivot Table Options, Data Tab, de-select the option "Save source data with the file", you can do this before or after sending the worksheet to a new Workbook that you will use for distribution.
Feb 16 2018 02:31 PM
<<<I can confirm the behaviour and I agree it is silly! I'll make sure MSFT gets this feedback.>>>
Thank You Jan. Not sure why this would be implemented like this... I can only hope this is rolled back in a future build.
Feb 16 2018 02:33 PM
Feb 16 2018 09:50 PM - edited Feb 19 2018 07:25 AMSolution
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):
Feb 19 2018 07:20 AM
Feb 19 2018 10:19 AM - edited Feb 20 2018 09:55 AM
Need to check Hayhams’s method proposed above.... but rest assured, we’ve been using the latest builds (updated automatically) and this just started this month for us.
Feb 20 2018 09:57 AM
Feb 20 2018 11:16 PM
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
Feb 21 2018 08:49 AM
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,
Feb 21 2018 11:55 AM
Mar 06 2018 08:39 AM
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.
Mar 07 2018 01:31 AM
May 15 2018 02:40 PM
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
Jul 04 2018 03:46 PM
July 4th, 2018 -- this issue is still happening. Inconsistent too, because it happened briefly months ago and then vanished the next day for me. And now it's occurring again in a document that didn't have the issue 1 month ago. Very strange.
Sep 11 2018 04:45 PM
Sep 18 2018 12:59 PM
Sept 18, 2018 - Here's VBA that copies values and formats for an entire sheet (including pivots) to a temporary sheet and saves that sheet as a new file with the same name as the tab. I'm a VBA hack, so any suggested improvements to this code are appreciated. Good luck and thanks in advance for any feedback on this coding.
'This macros copies the selected to sheet to a new sheet with similar formatting and with formulas/links/pivots removed
Dim SourceSheet As Worksheet
Dim TempSheet As Worksheet
Dim NewName As String
If MsgBox("Copy specific sheet to a new workbook" & vbCr & _
"New sheet will be pasted as values, named ranges removed" _
, vbYesNo, "NewCopy") = vbNo Then Exit Sub
.ScreenUpdating = False
' Modified - Original Source:http://www.vbaexpress.com/kb/getarticle.php?kb_id=359
' Copy active sheet
Set SourceSheet = ActiveWorkbook.ActiveSheet
' ***Insert new temporary sheet-per https://www.mrexcel.com/forum/excel-questions/607515-copy-paste-pivot-table-values-formatting.html
Set TempSheet = ThisWorkbook.Worksheets.Add
Application.CutCopyMode = False
' File name equal to sheet name
NewName = SourceSheet.Name
' Formatting of TempSheet
ActiveWindow.DisplayGridlines = False
ActiveWindow.Zoom = 70
ActiveSheet.Name = SourceSheet.Name
' Save it with the NewName and in the same directory as original
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & NewName & ".xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
.ScreenUpdating = True
' ***Delete temporary Worksheet-This section may not be needed
Application.DisplayAlerts = False
Application.DisplayAlerts = True
Set TempSheet = Nothing
' ***NOTE 1: This macro should be saved with the Workbook (vs Personal Macro Workbook) for the resulting new file to be saved in the original directory.
' ***NOTE 2: To work on a Mac, the above SaveAs command needs to be replaced by: ActiveWorkbook.SaveAs "Macintosh HD:Users:USERNAMEHERE:Desktop:DESKTOPFOLDERNAMEHERE:" & NewName & ".xlsx", FileFormat:=51