VBA Copy Pivot to new Workbook format issues

[funkyman]

I am copying a Pivot table from one workbook to another.

The Paste values & columns widths works fine but the paste formats only works properly (colours, text etc.) when the Pivot is not filtered, but if any filters are applied in Pivot only some of the text formatting is pasted into new sheet, none of the cell formatting is copied:

Code:

Private Sub CommandButton1_Click()

Dim FileName As String

Worksheets("PDLGenerator").Range("A3:H69").Copy ' copy the full Pivot chart range
Set NewBook = Workbooks.Add

NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False ' Paste values into new sheet
NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteFormats ' Paste formats into new sheet
NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteColumnWidths ' Paste columns widths into new sheet

On Error Resume Next
FileName = Range("C1").Value & " PDL.xlsx" 'save new workbook as "Project Name from sheet" + " PDL.xlsx"
NewBook.SaveAs FileName


End Sub

 

[SpillerBD]

The PivotTable's formatting are tied to the PivotTable Object. When you Past Values you are omitting the Object and hence the PivotTables formatting.
You can copy the Pivot Table in its entirety except for the connection to the source data. Look in the Options for the Pivot Table. As for the VBA to do it, I haven't but just knowing the regular steps should help use the recorder to find the basic code for what may be a better solution.

Otherwise you would need to apply Table formatting to the copied data and then apply a Table Format. Of course many PivotTables are much more complex for that to work...

 

[funkyman]

The PivotTable's formatting are tied to the PivotTable Object. When you Past Values you are omitting the Object and hence the PivotTables formatting.
You can copy the Pivot Table in its entirety except for the connection to the source data. Look in the Options for the Pivot Table. As for the VBA to do it, I haven't but just knowing the regular steps should help use the recorder to find the basic code for what may be a better solution.

Otherwise you would need to apply Table formatting to the copied data and then apply a Table Format. Of course many PivotTables are much more complex for that to work...

Thanks SpillerDB

I tried all the options for Paste and the only ones that copy the Pivot formats also copy all the data, whether it is visible or not.
I discovered that the Code for pasting works perfectly when only the visible Pivot rows are copied.
I think the solution is in adapting the copy command to only copy the visible rows.. all help appreciated!

Mark

 

[funkyman]

I found a solution which seems to work, probably breaking lots of coding rules: This just selects the visible cells before copying them and the rest of the code works fine.

Code:

Private Sub CommandButton1_Click()

Dim FileName As String

Worksheets("PDLGenerator").Range("H3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToLeft)).Copy

Set NewBook = Workbooks.Add

NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteFormats

On Error Resume Next
FileName = Range("C1").Value & " PDL.xlsx"
NewBook.SaveAs FileName


End Sub

 

[SpillerBD]

no rules. just guidelines.

I was just thinking about why you might want to do this. ?

PivotTables have a cool capability found in the Pivot Table Options, "Show Report Filter Pages..."
I use it to create specific Reports where a new worksheet is created with each filter (from the Filters section) for each new sheet.
If I am looking for another Pivot Table I just copy the worksheet that includes the Pivot Table. My Pivot Tables reside by themselves on a worksheet.