Forum Discussion
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 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
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!
28 Replies
- 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!
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 SubHope this helps.
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.
- Thank you - this is the only thing that worked for me.
I looked everywhere and tried the previous suggestions without consistent success. Now I know why. Thank you!
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!
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.
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!
- 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.
OMG you're brilliant !Haytham Amairah Finally my problem is settled using Format Painter. TQVM!
- 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
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.
- <<<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-)
- I can confirm the behaviour and I agree it is silly! I'll make sure MSFT gets this feedback.
<<<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
