Using: MS OS XP
Excel 2003

I have a pivot table that needs to be reformatted each time you change data.
For example going from all years to 2003. I have created a macro that will
re-format as needed.

I have found no way to trigger this macro except manually via a control. I
would like the macro to run every time the worksheet changes due to making
different selections.

I have tried the worksheet change event but it doesnt seem to work on the
pivot table, I can get it to work on a regular worksheet.

Please help.

Thanks in advance.

--
Richard

If you're changing the pivot table, you can use the
Worksheet_PivotTableUpdate event.

Richard wrote:
Using: MS OS XP
Excel 2003

I have a pivot table that needs to be reformatted each time you change data.
For example going from all years to 2003. I have created a macro that will
re-format as needed.

I have found no way to trigger this macro except manually via a control. I
would like the macro to run every time the worksheet changes due to making
different selections.

I have tried the worksheet change event but it doesnt seem to work on the
pivot table, I can get it to work on a regular worksheet.

Please help.

Thanks in advance.



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra, after reading your post I tried the Worksheet_PivotTableUpdate event.
I guess I don't know enough VBA to write it.

I have a macro I wish to run when the changes occur. The Macro name is
"Format_Pivot_Table".

Can you give me some direction on writing this?

Thanks in advanced.
--
Richard


"Debra Dalgleish" wrote:

If you're changing the pivot table, you can use the
Worksheet_PivotTableUpdate event.

Richard wrote:
Using: MS OS XP
Excel 2003

I have a pivot table that needs to be reformatted each time you change data.
For example going from all years to 2003. I have created a macro that will
re-format as needed.

I have found no way to trigger this macro except manually via a control. I
would like the macro to run every time the worksheet changes due to making
different selections.

I have tried the worksheet change event but it doesnt seem to work on the
pivot table, I can get it to work on a regular worksheet.

Please help.

Thanks in advance.



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

For Excel 2002 and later versions:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Format_Pivot_Table
End Sub


For earlier verions of Excel, you can use the Worksheet_Calculate event
to run the code.

Private Sub Worksheet_Calculate()
Format_Pivot_Table
End Sub


Richard wrote:
Debra, after reading your post I tried the Worksheet_PivotTableUpdate event.
I guess I don't know enough VBA to write it.

I have a macro I wish to run when the changes occur. The Macro name is
"Format_Pivot_Table".

Can you give me some direction on writing this?

Thanks in advanced.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html