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

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.