copy pivot table values and formatting only

Hello,

In Excel 2007, is it possible to copy & paste a pivot table, and have the result look like a pivot table, but not actually be a pivot table? I want to keep the values and the formatting (the colors and borders, etc) but I want it to not actually be linked to the data or have the ability to change with dropdowns, etc. I've tried the usual copy & paste special (values) thing, and the other otions in the paste special box, but it doesn't keep the formatting.

Just use a straight Copy & Paste.

Excel will paste the values and formats, including the style of the Pivot Table.

No, the result of that is an actual working pivot table, which I do not want. I only want it to look like a pivot table.

I tried that too, but the result is a rather lousy quality image, and then then people can't use the data in calculations.

The simple method of copy -> paste special (values) -> paste special (formats) worked fine in excel 2003, but now that 2007 uses pivot table styles, I can't figure out how to copy them.

No, the result of that is an actual working pivot table, which I do not want. I only want it to look like a pivot table.

The simple method of copy -> paste special (values) -> paste special (formats) worked fine in excel 2003, but now that 2007 uses pivot table styles, I can't figure out how to copy them.

In Excel 2007 that is true. I was using a workbook (97-2003) in compatibility mode.

However, in a fresh 2007 workbook after creating a PT and applying a style, I did a Copy and Paste Special Values, followed by Formats and then saved and closed the workbook. The format was unchanged when I re-opened the file.

When is the formatting lost in your process?

I just had time to get back to this... If anyone's interested, I got it to work:

Sub Copy_Sheet_With_Pivot_Table_Formatting()

Dim SrcSheet As Worksheet
Dim DestSheet As Worksheet
Dim Pt As PivotTable
Dim PtCol As Integer
Dim PtRow As Integer

    Set SrcSheet = ActiveSheet
    Cells.Select
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    Set DestSheet = ActiveSheet
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    
    SrcSheet.Select
    For Each Pt In SrcSheet.PivotTables
        Pt.TableRange2.Copy
        PtCol = Pt.TableRange2.Cells.Column
        PtRow = Pt.TableRange2.Cells.Row
        DestSheet.Cells(PtRow, PtCol).PasteSpecial xlPasteFormats
    Next Pt
    
    Application.CutCopyMode = False
    Cells(1, 1).Select
    DestSheet.Select
    Cells(1, 1).Select

End Sub

It looks a bit redundant, but the redundancy is necessary to copy all formats both inside and outside of pivot tables on a sheet that has both. Thanks to all who helped!

I tried this code, but it doesn't seem to work for me. The formatting of the pivot table is not retained. Any suggestions. Tks

That code works great if there isn't a filter on the pivot table; however, it doesn't seem to work if there is a filter in the pivot table. I think it has to do with the fact that the table has multiple ranges when there's a filter. Any ideas on how to get it to work when there's a filter in the pivot?

In recreating your actions several different ways, I figured it out, at least partly, so thank you Palmetto for helping me with that.

Copy -> paste special (values) -> paste special (formats) works in 2007 as long as only the pivot table is selected. If I try to copy the pivot table plus any cells outside the pivot table, then the paste special (formats) doesn't work. So if I was trying to copy only a pivot table, the problem would be solved.

However, I'm sometimes trying to copy a whole sheet, only part of which is a pivot table. I can copy the whole thing and paste special (values), but then I have to go back and copy only the pivot table and paste the formats in the correct place on the destination sheet. And sometimes there is more than one pivot table on a sheet.

How would I do that part in a macro - for each pivot table on sheet, select only the pivot table, copy it, and paste the formats in the same place on the destination sheet? The pivot tables could of course vary in location and size.

In Excel 2007:

1.. copy the exact range of the pivot from beginning to end
2. copy and paste special values
3. open clipboard
4. just click on the latest copy instance once - this copies the pivot formats as it is on top of the values you just pasted
5. but the drawback is you cant copy and paste at the same place as the original pivot. for that you need to do the above steps, then move the pivot to another location, and then copy and paste back the values with the new formatting just done

regards
Ravi.