You can create custom PivotTable Styles in an Excel 2007 workbook, to fine tune the appearance of your pivot tables. If you’ve invested a lot of time in create a custom style, you might like to copy that style to a different workbook.
Unfortunately, there isn’t a built-in command that will let you copy PivotTable Styles from one workbook to another. However, you can follow a few simple steps to copy your styles to any workbook.
Copy a PivotTable Style
To copy a custom PivotTable style, you can temporarily copy a formatted pivot table to a different workbook. In this example, we’ll copy a custom PivotTable Style – MyMedium2 – from the MyOld.xlsx workbook to the MyNew.xlsx workbook.
- In the MyOld.xlsx workbook, select a cell in the pivot table that has the custom style applied.
- On the Ribbon’s Options tab, in the Actions group, click Select.
- Click the Entire Pivot Table command.
- On the Ribbon’s Home tab, click Copy.
- Switch to the MyNew.xlsx workbook.
- Select a blank worksheet, or insert a new blank worksheet.
- Select cell A1 on the blank worksheet.
- On the Ribbon’s Home tab, click Paste.
- Delete the sheet that contains the pasted copy of the pivot table.
Your custom PivotTable style now appears in the PivotTable Styles gallery.
Select any pivot table in the workbook, and apply your custom PivotTable Style.
Watch the Video
_____________________
For more information on Pivot Tables, please visit Pivot Table Topics index on the Contextures Website.
_____________________
Excellent!!!!! Thanks heaps
VBA version
Sub copy_Custom_Pivottable_style()
' copies custom PivotTable sytle from active Pivottable to all other open workbooks
Dim wbk As Workbook
Dim pt As PivotTable
With Application
On Error Resume Next
Set pt = ActiveCell.PivotTable
On Error GoTo 0
If Not pt Is Nothing Then
.DisplayAlerts = False
For Each wbk In .Workbooks
If wbk.Name ActiveWorkbook.Name Then
pt.TableRange2.Copy
With wbk.Sheets.Add
.Paste
.Delete
End With
End If
Next wbk
.DisplayAlerts = True
Else
MsgBox "Select cell in pivot table and try again."
End If
End With
End Sub
Hi, In Office2019 / O365 copying pivot table will not work as it is pasted without format. Instead you can copy a sheet with pivot table to a new worksheet and then delete it and this works!
Thanks for help with it!