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.
Thank You,
Brian Catalano
Jan 09 2019 10:51 PM
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.
Apr 02 2019 11:38 PM
OMG you're brilliant !@Haytham Amairah Finally my problem is settled using Format Painter. TQVM!
Jun 19 2019 07:46 AM
@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!
Aug 18 2019 01:23 PM
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.
Jan 15 2020 02:19 AM
Sep 20 2020 08:17 PM
Sep 21 2020 03:37 AM
Feb 24 2021 03:23 PM
I looked everywhere and tried the previous suggestions without consistent success. Now I know why. Thank you!
Jun 24 2021 12:34 PM