Author: Oscar Cronquist Article last updated on December 04, 2012

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?

  1. Open VB Editor (Press Alt+F11)
  2. Double click on Thisworkbook in the project explorer window
  3. Paste code to module
  4. Return to excel