Pivot Tables and Automatic Column Width 2

[SaintAugustine]

Hi all,

I'm working (peripherally) on something that automatically generates many pivot tables to help sort data. There's a pivot table on each tab.

A problem I'm encountering is when I choose a different sort option from the Pivot Table's dropdown menu (in the worksheet itself, not in the Pivot Table submenu), the width of the cell that contains the option doesn't adjust automatically. This is a problem on *some* spreadsheets, but not all!

It looks ugly - and we need it to look nice. Our VBA guy thinks it's something I did, I think it's something he did. Can anyone help me out with this? Is there a way to make the column width adjust automatically?

 

[nberryman]

Ask the VB guy to look at this

Private Sub Worksheet_Change(ByVal Target As Range)
Columns("A:F&quot.AutoFit
End Sub



Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[ETID]

Try recording the option that you set as a macro

you'll get a resulting code that looks something like this

With ActiveSheet.PivotTables("PivotTable1&quot
.HasAutoFormat = True
.PreserveFormatting = False
End With


then set your true's and false's to suite your needs

(loop this for each pivot created)

 

[SaintAugustine]

Thanks guys - I'll send this to the guy and see what he says