Geek asks and answers

Copy Pivot Table Formats

I'm having trouble getting pivot table formats to copy to a new sheet. I am basically trying to do the following:

someRange.Copy
someOtherRange.pasteSpecial xlPasteValues
someOtherRange.pasteSpecial xlPasteFormats

Basically, I want an exact copy of the current view in the pivot table on a new sheet, but I just need the values ​​and formatting (and not connect to the pivot table anymore). Performing this procedure manually gives the correct result, but for some reason the formatting is not copied when I use the same approach via vba ... Thoughts?

Update: Exact code as requested:

Application.CutCopyMode = False
pivotSheet.Range(pivotSheet.usedRange.Cells(1, 2), pivotSheet.Cells(pivotSheet.usedRange.Rows.Count, pivotSheet.Columns.Count)).Copy
updatesSheet.Range(updatesSheet.Cells(1, 1), updatesSheet.Cells(pivotSheet.usedRange.Rows.Count, pivotSheet.usedRange.Columns.Count)).PasteSpecial xlPasteValues
updatesSheet.Range(updatesSheet.Cells(1, 1), updatesSheet.Cells(pivotSheet.usedRange.Rows.Count, pivotSheet.usedRange.Columns.Count)).PasteSpecial xlPasteFormats
Application.CutCopyMode = False
+3
source share
2 answers

TEST AND TEST

Option Explicit

Sub Sample()
    With Sheets("Sheet1")
        .Range("H1:I6").Copy

        With .Range("M7")
            .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

            .PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        End With
    End With
End Sub

Snapshot

enter image description here

+3
source

. , , . , , , .

0

More articles:


All Articles