Automatically Update Pivot Charts

[Moily]

Hi there,

I've got one sheet in a large workbook (Excel 2003) that is made up of Pivot
Charts. I would like for those charts to be updated everytime the sheet is
opened within the book. I think a macro would probably be best (but please
tell me otherwise if you know another method!) and am using the below code
but there are a few difficulties:

It's meant to update all the pivot tables in the sheet as soon as it's
opened and then readjust the column widths. The problem is that it isn't
isolated just to the one worksheet (called "CrossTabQs") and instead runs
this macro for ALL the sheets in the workbook. Can anyone help? Thank you in
advance!!!! - Ann

Code as follows:

Sub Auto_Open()
Application.OnSheetActivate = "UpdateIt"
Application.OnSheetActivate = "AdjustColumns"
End Sub

Sub UpdateIt()
Dim iP As Integer
Application.DisplayAlerts = False
For iP = 1 To ActiveSheet.PivotTables.Count
ActiveSheet.PivotTables(iP).RefreshTable
Next
Application.DisplayAlerts = True
End Sub

Sub AdjustColumns()
Cells.Select
Range("A6").Activate
Selection.ColumnWidth = 26.14
Columns("B:H").Select
Range("B6").Activate
Selection.ColumnWidth = 16.43
Range("E50").Select
End Sub

 

[Socko]

You can select any cell in pivot table, do right click with mouse, and
you will find a pop up menu. In the menu, select TABLE OPTIONS, which
will give you a dialog... Look for "Refresh on Open" checkbox, and
cilck the checkbox. This will ensure that whenever you open, the pivot
table refreshes.

I hope this helps...

Selva V Pasupathy
For more resources on
Excel, VBA, & other resources,
please visit: http://socko.wordpress.com

 

[Moily]

Thanks Socko but one more thing - the columns all go out of whack when it's
been updated - I really don't want the columns to fit to the size of the data
within - I prefer them to be a set width. Is there any way around that?

Cheers,
Ann

 

[Roger Govier]

Hi Molly

I think I would change the opening code to

Sub Auto_Open()
UpdateIt
AdjustColumns
End Sub

then make sure the next 2 macros are in a standard module, Insert >Module
rather than on any sheet.
and ensure that you activate the sheet name where you want to adjust
columns.

Sub UpdateIt()
Dim iP As Integer
Application.DisplayAlerts = False
For iP = 1 To ActiveSheet.PivotTables.Count
ActiveSheet.PivotTables(iP).RefreshTable
Next
Application.DisplayAlerts = True
End Sub

Sub AdjustColumns()
Sheets("your_required_sheet").activate '<== Change to your sheetname
Cells.Select
Range("A6").Activate
Selection.ColumnWidth = 26.14
Columns("B:H").Select
Range("B6").Activate
Selection.ColumnWidth = 16.43
Range("E50").Select
End Sub

 

[Moily]

Thanks Roger that's perfect - exactly what I was looking for!