How to copy a pivot as values without losing formatting

I have a small little pivot table supported by 22,000 x 40 table.  It has a simple autoformat applied along with some other custom formatting and headers.  I want to send this pivot table without the supporting data because I don't want to send a 4MB Excel file for the sake of the 20k worth of pivot table I actually care about.

The problem is that if I select the pivot table and copy paste as values, the formatting gets erased.  The only partial solution I have found is to copy the pivot table, paste it into a blank range as formats, then copy and paste the pivot table as values, then copy and paste back the formatting, then delete the temp range with the formatting.  I could almost live with that convoluted way of doing things, but I run into a different problem doing it with a pivot table that has a report filter added.  When you try to select the pivot including the report filter row, it simply refuses to paste the formats anywhere else.  Now I have to copy and paste the pivot table formatting excluding the report filter into a blank range, the copy and paste the pivot tables as values, then copy back the formatting from the temp range, then manually apply the format to the report filter range.

What is the deal with this odd behavior?  Why isn't there a simple one-click to convert a pivot table to values?  I would think that would be a pretty common thing to do.

I have tried to code together a solution, but it leaves a lot to be desired.  Basically what it does is copy the entire pivot table to a temp workbook, then change the pivot table to values, then copy and paste the formatting cell-by-cell back to the original pivot.  It has to go cell-by-cell because of the issue with paste formats not working when there is a report filter.  But cell-by-cell processing makes for slow execution on anything but the smallest pivot tables.  And also, for good measure, I ran into another bug in which the paste formats does not go to the correct row unless screenupdating is turned on.  I have yet to figure that one out.

Sub PivottoValuesKeepFormatting()
Dim bkNew As Workbook
Dim bkCur As Workbook
Dim r As Range
Dim r2 As Range
Dim l As Long

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Set r2 = Selection
    r2.Copy
    Set bkCur = ActiveWorkbook
    Set bkNew = Workbooks.Add
   
    Range("A1").PasteSpecial xlPasteAll
           
    bkCur.Activate
    Selection.PasteSpecial xlPasteValues
    bkNew.Activate
    Application.ScreenUpdating = True 'I don't know why, but the formats end up in the wrong place if you don't refresh the screen
    Application.ScreenUpdating = False
    For Each r In Selection
        l = l + 1
        r.Copy
        bkCur.Activate
        r2.Cells(l).PasteSpecial xlPasteFormats
        bkNew.Activate
    Next
   
    bkNew.Close savechanges:=False
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

 Copy the pivot table into another workbook.

In the new workbook, right-click on a cell in the pivot table, and click on PivotTable Options

On the Data tab, remove the check mark for Save Source Data with File.

This will preserve the pivot table, but not the data, so it should look the same, but would be non-functioning.

I tried this and it does not work.  Are you copying just the table itself, the whole spreadsheet, by rows, etc?  I've done it using all of those.  Once I paste it into the new workbook, I remove the check mark and save the file with a new name (I've done it without saving too).  I then try to copy and paste the table into a new workbook, but it still will not carry over the formatting and has the source data behind it.  What I'm a doing wrong?

Thanks!

I copied the range that contained the pivot table, and pasted it into the new workbook.

In the copied pivot table, I turned off the option to save the source data with the pivot table.

This leaves the pivot table and its formatting intact, but when it's emailed to someone else, they won't have access to the original file, and therefore they won't be able to refresh the pivot table, to reconnect to the source data.

If you're trying this on the same computer where the original source file is stored, the pivot table will be able to reconnect, and you'd have access to the source data again.

Same here.

Solution = Goto Pivot table options as before.

Display tab this time - uncheck "Show expand/collapse buttons" and "Display field captions and filter drop downs"

Sorted?

Worked perfect, thanks for the information.