How to auto fit columns in pivot table drill down page

[skinpup]

Hello


When you double click on a figure on a pivot table and it opens the details on a separate tab, how do you get that page to auto fit the data?

I have to send the table out to staff who aren't very Excel savvy and asking them the select the sheet and expand the columns and row to fit the data is not an option.


I have posted this on a couple of other sites but never get an answer.


Using Excel 2010

 

[Hui]

Skinpup


You should have tried here first!


Try the following code

It must be placed in the Workbook module in VBA

[pre]

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
End Sub

[/pre]

Whenever a new Worksheet is created it will autofit the contents to the column widths and leave the cursor in A1

It will also run whenever you manually add a new sheet, but you won't see any changes in that case

 

[skinpup]

Thanks I'll give it a go and let you know how I get on.

 

[skinpup]

Hello


Sorry for the delay been busy with work. Couldn't get this to work i have copied the code in to a module in the VBA personal workbook but not sure how to get it to work?!


Any tips for a complete VBA idiot?


Martin

 

[Hui]

Martin

The file must be copied into the Workbook Module NOT a Worksheet or Code Module

The file must be saved as a *.xlsm or *.xlsb file type only


If that doesn't help can you post your file somewhere?

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook