Turn Off Pivot Table Autofit Column Width On Update Setting

VBA To Turn Off AutoFit Column Width For All Pivot Tables

What This VBA Code Does

If you live dangerously and have multiple Pivot Tables sporadically on a single sheet (I’ve dabbled a few times…), you may have noticed whenever you refresh your Pivots, the column widths can get screwed up. This is because by default Excel will autofit the column width specifically for the Active Pivot Table you refreshed (ignoring width requirements of all other Pivot Tables). This consequently can make data in your other Pivot Tables appear in the dreaded “###” format!

After constantly having to go through and re-adjust my column widths in a particular file of mine, the decision was made that I needed to turn off the Pivot Table setting called “Autofit column widths on update”. Now I didn’t want to go through and manually change this setting for all my pivot tables, so I quickly whipped up a VBA macro to do it for me!

VBA Code:

This VBA code will cycle through each Pivot Table on the currently ActiveSheet and uncheck the Autofit column widths on update setting.

Sub TurnAutoFitOff_PivotTables()
'PURPOSE: Turn off Autofit Column Width On Update Setting for every Pivot Table
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

Dim pvt As PivotTable

For Each pvt In ActiveSheet.PivotTables
  pvt.HasAutoFormat = False
Next pvt

End Sub

How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris
Founder, TheSpreadsheetGuru.com