Pivot Tables and macro

frajaro · Mar 14th, 2002, 05:33 PM

Dear MrExcel,

I have been working with Pivot tables for a long period and getting more and more triks from experience and your useful web. Now, I'm trying to improve a file for data analysis by using macros connected with Pivot tables. My question is:

¿Is there any way to execute a macro each time I select an option of the Pivot Table?

I'd appreciate very much your answer,
thank in advance

Francisco

Corni · Mar 14th, 2002, 09:44 PM

Use a Worksheet_Change event procedure for the worksheet containing the pivot table.Open Project Explorer (Ctrl+R), locate the workbook, expand for worksheet objects and double click on the appropriate worksheet icon. When the code window opens, select the Change event and insert the code to be run.

frajaro · Apr 9th, 2002, 04:34 AM

Thanks a lot for the advice.
I would like to have your comments to the following case related to the same issue:

When I use the calculate event procedure for the worksheet containing my Pivot Table it works well. Howewer, when I try to modify or change any cell in other worksheet or workbook the macro is also executed. I noticed this happens only when there is a function INDIRECT included in the worksheet containing the pivot table.

Is seems that INDIRECT function forces the activation of the change/calculate event procedure after any change in any sheet or workbook. (although the procedure is just associated to worksheet1)

¿do you know how to solve this unwished activation of the event?

regards
Francisco (frajaro)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 14th, 2002, 05:33 PM	#1
frajaro New Member Join Date: Mar 2002 Posts: 13	Dear MrExcel, I have been working with Pivot tables for a long period and getting more and more triks from experience and your useful web. Now, I'm trying to improve a file for data analysis by using macros connected with Pivot tables. My question is: ¿Is there any way to execute a macro each time I select an option of the Pivot Table? I'd appreciate very much your answer, thank in advance Francisco

Mar 14th, 2002, 09:44 PM	#2
Corni Board Regular Join Date: Mar 2002 Location: Brampton Posts: 324	Use a Worksheet_Change event procedure for the worksheet containing the pivot table.Open Project Explorer (Ctrl+R), locate the workbook, expand for worksheet objects and double click on the appropriate worksheet icon. When the code window opens, select the Change event and insert the code to be run.

Apr 9th, 2002, 04:34 AM	#3
frajaro New Member Join Date: Mar 2002 Posts: 13	Thanks a lot for the advice. I would like to have your comments to the following case related to the same issue: When I use the calculate event procedure for the worksheet containing my Pivot Table it works well. Howewer, when I try to modify or change any cell in other worksheet or workbook the macro is also executed. I noticed this happens only when there is a function INDIRECT included in the worksheet containing the pivot table. Is seems that INDIRECT function forces the activation of the change/calculate event procedure after any change in any sheet or workbook. (although the procedure is just associated to worksheet1) ¿do you know how to solve this unwished activation of the event? regards Francisco (frajaro)