SOLVED
Home

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

Brian Catalano
Occasional 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

23 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-)
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.
Highlighted

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

If you select ONLY the table of the pivot you can copy and paste values with the table formatting. 

Select only the header row and the data, if you include the filter row it won't work. If you include a blank row it won't work. if you include a blank column it won't work. 

 

Why this is the only way to get the formatting with the data I have no idea.

OMG you're brilliant !@Haytham Amairah Finally my problem is settled using Format Painter. TQVM!


@glumthrone8 wrote:

If you select ONLY the table of the pivot you can copy and paste values with the table formatting. 

Select only the header row and the data, if you include the filter row it won't work. If you include a blank row it won't work. if you include a blank column it won't work. 

 

Why this is the only way to get the formatting with the data I have no idea.


@glumthrone8 This is the real answer. All the other methods bring the formatting over, but do not bring the cell colors, borders, etc. Glad I scrolled all the way to the bottom to find this. Thanks!

If you want to copy the Pivot Table and paste it over itself as values along with formatting, you can try something like this...

 

Sub CopyPivotTableAndPasteBackAsValuesAndFormatting()
Dim ws As Worksheet
Dim pt As PivotTable
Dim lc As Long, r As Long, c As Long
Dim pstRng As Range, tmpRng As Range, tmpRng2 As Range
Dim ptRng1 As Range, ptRng2 As Range

Set ws = ActiveSheet
lc = ws.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 2
If ws.PivotTables.Count = 0 Then Exit Sub

Set pt = ws.PivotTables(1)

Set pstRng = pt.TableRange2.Cells(1)
r = pt.TableRange2.Rows.Count
c = pt.TableRange2.Columns.Count

Set tmpRng = ws.Cells(1, lc)
Set tmpRng2 = ws.Cells(3, lc)

Set ptRng1 = pt.TableRange1
Set ptRng2 = pt.TableRange2

MsgBox ptRng1.Address
MsgBox ptRng2.Address

If ptRng1.Address <> ptRng2.Address Then
    pt.TableRange2.Cells(1).CurrentRegion.Copy
    tmpRng.PasteSpecial xlPasteValues
    tmpRng.PasteSpecial xlPasteFormats
    
    pt.TableRange1.Copy
    tmpRng2.PasteSpecial xlPasteValuesAndNumberFormats
    tmpRng2.PasteSpecial xlPasteFormats
Else
    pt.TableRange1.Copy
    tmpRng.PasteSpecial xlPasteValuesAndNumberFormats
    tmpRng.PasteSpecial xlPasteFormats
End If

pt.TableRange2.Clear

tmpRng.Resize(r, c).Copy pstRng
tmpRng.Resize(r, c).Clear
End Sub

Hope this helps.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies