Disable autofit column widths for Pivot table
I read this interesting article 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.
Here are the steps to manually disable Excel resizing column widths automatically.
- Press with right mouse button on on any cell in the Pivot table.
- Press with mouse on "Pivot Table Options".
- Press with mouse on the "Layout and Format" tab, then press with left mouse button on the box next to "Autofit column widths on update" to uncheck it.
- Press with left mouse button on OK button to close the dialog box.
Lindsay Hughes commented:
I recommend that you create a personal macro workbook and save the following macros to that workbook. This will make it easier for you to change this setting automatically, simply run the macro to apply this setting to all Pivot tables in the active workbook.
If you use it really often then I recommend adding the macro to the Quick Access Toolbar located at the very top of Excel.
The following Event macro disables this setting for a Pivot Table named PivotTable1 in a worksheet named Sheet1. An event in Excel is a thing that happens that triggers something else to happen.
In this case, if any worksheet in the workbook is activated the code is rund.
'Event code is exectued when any sheet is activated Private Sub Workbook_SheetActivate(ByVal Sh As Object) 'Save False to HasAutoFormat property for worksheet Sheet1 and PivotTable named PivotTable1 Worksheets("Sheet1").PivotTables("PivotTable1").HasAutoFormat = False End Sub
I can't change it to the default setting but the code below automatically disables this setting for all pivot tables on the active sheet.
Private Sub Workbook_SheetActivate(ByVal Sh As Object) 'Dimension variable and declare datatype Dim pt As PivotTable 'Iterate through pivottables in active worksheet For Each pt In ActiveSheet.PivotTables 'Change property HasAutoFormat to False pt.HasAutoFormat = False 'Continue with next pivot table Next pt End Sub
This macro changes the setting for all pivot tables in all worksheets in the active workbook.
Private Sub Workbook_SheetActivate(ByVal Sh As Object) 'Dimension variables and declare datatypes Dim pt As PivotTable Dim WS as Worksheet 'Go through each worksheet in the worksheets object collection For Each WS In Worksheets 'Iterate through pivottables in active worksheet For Each pt In WS.PivotTables 'Change property HasAutoFormat to False pt.HasAutoFormat = False 'Continue with next pivot table Next pt 'Continue with next worksheet Next WS End Sub
The following macro disables the setting for pivot tables in all open workbooks
Private Sub Workbook_SheetActivate(ByVal Sh As Object) 'Dimension variables and declare datatypes Dim pt As PivotTable Dim WS as Worksheet Dim j As Single 'Go through all open workbooks For j = 1 To Workbooks.Count 'Go through each worksheet in the worksheets object collection For Each WS In Workbooks(j).Worksheets 'Iterate through each pivot table For Each pt In WS.PivotTables 'Change property HasAutoFormat to False pt.HasAutoFormat = False 'Continue with next PivotTable Next pt 'Continue with next WorkSheet Next WS 'Continue with next workbook Next j End Sub
Where to put the code?
- Copy above VBA code.
- Press Alt+F11 to open the Visual Basic Editor.
- Double press with left mouse button on your workbook to expand the object list.
- Double press with left mouse button on Thisworkbook in the project explorer window to show the workbook module.
- Paste VBA code to the workbook module.
- Exit VB Editor and return to Excel.
Pivot table category
Table of Contents Introduction to pivot tables Create pivot table Group data Analyze data (pivot table) Compare performance, year to […]
Rodney Schmidt asks: I am a convenience store owner that is looking to make a spreadsheet formula. I want this […]
In a previous post:Â How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot […]
Excel categories
3 Responses to “Disable autofit column widths for Pivot table”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
[...] 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 press with left mouse button on Thisworkbook in the project explorer window?