pivot table copy and paste values AND formating...

in excel 2010, I'm using the following to copy and paste values and formating from a pivot table, but i lose the formatting (TableStyle2 = "PivotStyleLight8"):

Selection.CurrentRegion.Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

i have tried to add, xlPasteFormats, but to no avail...?

thank you.

Re: pivot table copy and paste values AND formating...

thank you.

from your example, the copy does work (to another range) and the formatting stays, but so does the pivot table.
(i'm trying to copy the values of the pivot table (this works) and also copy the pivot table formatting (.TableStyle2 = "PivotStyleLight8") - this doesn't in my example)...

?

thank you.

Re: pivot table copy and paste values AND formating...

and thanks to Debra Dalgleish, i used:

Set pt = ActiveCell.PivotTable
Set rngPTa = pt.PageRange


    Set rngPT = pt.TableRange1
    lRowTop = rngPT.Rows(1).Row
    lRowsPT = rngPT.Rows.count
    Set ws = Worksheets("abc")
    Set rngCopy = rngPT.Resize(lRowsPT - 1)
    Set rngCopy2 = rngPT.Rows(lRowsPT)
    
    rngCopy.Copy Destination:=ws.Cells(lRowTop, 11)
    rngCopy2.Copy Destination:=ws.Cells(lRowTop + lRowsPT - 1, 11)

this works if copied to anther range, but not if i try to copy, paste values and formats to itself (same range) and "break" pivot table...?

thank you.