Maintaining pivot table column widths

[peterbel]

Is there a way using VB to make sure that when pivot tables are refreshed their column widths are NOT changed?

I have about 10 pivot tables on a spreadsheet tab, all derived from a master table on another tab. When I add new data to the master I refresh the derived tables using VB behind a button on the sheet. Excel then decides to alter column widths making a lot of the data unreadable unless widths are reset.

Thanks

 

[Yongle]

Without VB
- set separately for each table

Pivot Table Options \ Layout and Format \ uncheck Preserve Cell Formatting on Update

 

[Yongle]

Using VBA

Single pivot table, using its name

ActiveSheet.PivotTables("PT1").HasAutoFormat = False

or loop entire workbook like this

Sub KeepFormat()
    Dim ws As Worksheet, pt As PivotTable
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
           pt.HasAutoFormat = False
        Next pt
    Next ws
End Sub

 

[peterbel]

Thanks for your repies.
I have tried the 'manual' alteration first so I can test it does as expected. I will add the VB if it does.
Saved me a lot of re-formatting time, thanks.