Disable autofit column widths for all pivot tables in a sheet
I read this post Quick Trick: Resizing column widths in pivot tables on the Microsoft Excel blog. It is about Excel automatically making column widths too wide when using urls in pivot tables. Stacey Armstrong demonstrates how to disable this setting.
Lindsay Hughes commented:
This should be the default setting, or there should be some way to enable this as the default setting. I have to do this everytime I make a pivot table (which can be a dozen times a day in some projects)
I can´t make it the default setting but the code below automatically disables this setting for all pivot tables in an activated sheet.
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim pt As PivotTable
For Each pt In ActiveSheet.PivotTables
pt.HasAutoFormat = False
Next pt
End Sub
Where to copy code?
- Open VB Editor (Press Alt+F11)
- Double click on Thisworkbook in the project explorer window
- Paste code to module
- Return to excel
Related posts:
Quickly create links to sheets, tables, pivot tables and named ranges in a workbook
Google spreadsheets: Pivot tables
Analyze trends using pivot tables
Recommended categories
[...] Disable autofit column widths for all Pivot Tables in a sheet (VBA) [...]
The code above did not work for me. I pasted into the suggested location and the pivot tables are still autofitting on update.
Is there something I am missing?
Thanks!
Bob,
I tried the code and it works in in excel 2007 and 2010.
Did you double click on Thisworkbook in the project explorer window?