SOLVED

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

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

28 Replies

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.

It do not work if you paste over the save plase. You paste as value but after paste as format is not possilbe any more.
Thinking outside the box (MS Office)
Try pasting the pivot table into a Google Sheet, and then copy the selection from the Google Sheet into MS Excel.
It worked for me.
Regards
This works, thank you!

@glumthrone8 

I looked everywhere and tried the previous suggestions without consistent success. Now I know why. Thank you!

Thank you - this is the only thing that worked for me.