SOLVED

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

Copper Contributor

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. 

 

Thank You,
Brian Catalano

28 Replies
I can confirm the behaviour and I agree it is silly! I'll make sure MSFT gets this feedback.

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.

<<<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.

 

Brian

<<<You can share a Pivot Table with the formatting without the underlying data.....>>>

Regardless of whether the "Save source data with the file" box is checked or not, drill-down is possible... which is what I need to avoid. There are fields in the Pivot that I cannot share with the genpop.... 8-)
best response confirmed by Brian Catalano (Copper Contributor)
Solution

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):

  1. Highlight the first PivotTable and copy it.
  2. Go to another location, and press Ctrl+Alt+V to open the Paste Special dialog box.
  3. Select Values and then hit OK.
  4. Press Ctrl+Alt+V again.
  5. Select Formats and then hit OK again!
Looks like this "issue" has been in Excel for at least 6 years, as Excel 2010 also shows this behavior. So your workaround is the way to go.

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.

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!

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

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

Thanks for clarifying the situation, that certainly explains things!

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.....................

Hi 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 

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.

This approach only works if going to "another location". It does not work if over writing the existing location using Office 2016 (365) and Mac OS. This issue only started when I recently switched from Excel 2011 to Excel 2016. Hopefully, there is a VBA solution of some sort. Thanks.

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.

 

Sub CopySheetsToFile()

   

'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

 

    With Application

       .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

       

       SourceSheet.Select

       Cells.Select

       Selection.Copy

      

       TempSheet.[A1].PasteSpecial Paste:=xlPasteValues

       

       SourceSheet.Select

       Cells.Select

       Selection.Copy

       

       TempSheet.[A1].PasteSpecial Paste:=xlPasteFormats

 

       Application.CutCopyMode = False

 

'      File name equal to sheet name

       TempSheet.Copy

       NewName = SourceSheet.Name

              

'      Formatting of TempSheet

       Range("A1").Select

       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

              

               

        ActiveWorkbook.Close SaveChanges:=False

     

        .ScreenUpdating = True

   

 '    ***Delete temporary Worksheet-This section may not be needed

       Application.DisplayAlerts = False

       TempSheet.Delete

       Application.DisplayAlerts = True

       Set TempSheet = Nothing

  

    End With

    Exit Sub

 

End Sub

 

'    ***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

1 best response

Accepted Solutions
best response confirmed by Brian Catalano (Copper Contributor)
Solution

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):

  1. Highlight the first PivotTable and copy it.
  2. Go to another location, and press Ctrl+Alt+V to open the Paste Special dialog box.
  3. Select Values and then hit OK.
  4. Press Ctrl+Alt+V again.
  5. Select Formats and then hit OK again!

View solution in original post