Formatting Pivot Tables on Multiple Worksheets

[SAMCRO2014]

I have 17 worksheets (by manager) with one pivot table per worksheet filtered to data to suit each manager's needs. All the headings are the same on each pivot table. There must be a way to format all the pivot tables the same way at the same time or via a loop but I can't figure it out. I tried recording it and got a whole bunch of coding which is making my macro too long and it will not run.

Any ideas? I was thinking along the lines:

For each PivotTable on the Worksheet
PivotTable. Activate
Insert format coding here
Next Worksheet

Can this be done?

 

[KennyGreens]

Re: Formatting Pivot Tables on Mutiple Worksheets

Hi there, you can try doing something like this:

For Each ws In ActiveWorkbook.Worksheets
    For Each pt In ws.PivotTables
        'Insert format code here
    Next pt
Next ws

If each value field should be the same format, you can add in

For Each df In pt.DataFields

 

[My Aswer Is This]

Re: Formatting Pivot Tables on Mutiple Worksheets

So which sheet has the Pivot table style you want copied to all other sheets
Would it be Sheet(1)
So you want sheets 2 to the last sheet in your workbook to have the same Pivot Table format as Pivot table on Sheets(1)
Is that true?

Then use this script:

Sub Format_Pivot_Table()
'Modified  10/19/2018  10:07:30 AM  EDT
Application.ScreenUpdating = False
Dim ans As String
Dim i As Long
ans = Sheets(1).PivotTables(1).TableStyle2
    For i = 2 To Sheets.Count
        Sheets(i).PivotTables(1).TableStyle2 = ans
    Next
Application.ScreenUpdating = False
End Sub

 

[SAMCRO2014]

Re: Formatting Pivot Tables on Mutiple Worksheets

These are both awesome!!! Thank you!!!!

 

[My Aswer Is This]

Re: Formatting Pivot Tables on Mutiple Worksheets

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

These are both awesome!!! Thank you!!!!