Automatically Run Macro After Pivot Table Refresh

    This seems like it should be fairly easy to do but is eluding me!


    I have a Pivot Table feeding a chart that needs specific formatting


    However, after a Pivot Table refresh, the chart loses any custom formatting and returns to the default formatting.


    (I know I can create custom user-defined charts but I don't want to do that here)


    What I would like to happen is for a (chart formatting) macro to automatically run after a pivot table refesh occurs. It should only occur on the one worksheet that contains the Pivot Table and associated chart and should run the macro after a refresh REGARDLESS of whether the data within the table was updated or not.


    Can anyone help me with the VBA code and which area within the VBA Project Manager the code should be placed?


    Thanks in advance for any responses


    ~CJ

    Re: Automatically Run A Macro After Pivot Table Refresh


    you can do that in the worksheet event... looks something like this


    Code
    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)


End Sub


    HTH


    z

    Re: Automatically Run A Macro After Pivot Table Refresh


    I discovered the answer on my own but will share it here to save others from searching for it and it is relatively easy...


    Right-Click on the worksheet that contains the PivotTable and then 'View Code'
    Choose 'Worksheet' on the (General) dropdown menu and choose 'PivotTableUpdate' on the (Declaration) dropdown menu.


    Now simply paste the following statement within to run any macro automatically after the Pivot Table is refreshed:


    Code
    [COLOR="red"]Application.Run "your macro"[/COLOR]


    The whole thing should look like this after you're done:


    Code
    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
[COLOR="Red"]Application.Run "[U]'Your Workbook name.xls'[/U]![U]Your Macro[/U]"[/COLOR]
End Sub


    Here's an example:

    Code
    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
[COLOR="SeaGreen"]Application.Run "'Workload  Stats.xls'!RecEntFormat"[/COLOR]
End Sub


    Hope this little bit of info helps others!


    I prefer this method for re-formating pivottable charts rather than creating user-defined custom charts because the user-defined custom charts only remain on the computer of whomever created them and this is not ideal if the spreadsheet will be shared among many users.

    Re: Automatically Run A Macro After Pivot Table Refresh


    Hi,


    What am I doing wrong?


    Code
    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)


[COLOR="Red"]Application.Run "'1pivotbasedon2.xlsm'!test2" [/COLOR]


End Sub



    My macro's name is test2 & the file name is '1pivotbasedon2.xlsm'


    - it's showing me a syntax error :(


    Pls help!

    Re: Automatically Run Macro After Pivot Table Refresh


    Are you actually trying to run this piece of code with the color tag?


    If so try changing it to


    Code
    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) 
     Application.Run "'1pivotbasedon2.xlsm'!test2"
 Sub


    Or you can call the procedure


    Code
    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) 
     Call ModuleName.test2
 Sub

    Re: Automatically Run Macro After Pivot Table Refresh


    Hey Craig,


    Just a quick follow up on my question.. in my case, there are two pivot tables involved.. because of which the PivottableUpdate event might get in to an endless loop, and gives me an error.


    Any way, we can tweak the syntax to trigger the macro upon refreshing only one pivot?


    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)



    Application.Run "'1pivotbasedon2.xlsm'!test2"

    End Sub


    Can I specify the pivot table name somewhere here in the syntax?


    Thanks much!

    Re: Automatically Run Macro After Pivot Table Refresh


    You need to add this bit of code before you run the actual code:


    Code
    Application.EnableEvents = False


    and remember to turn it back on before the code terminates


    Code
    Application.EnableEvents = True


    This will prevent the endless loop.

    Re: Automatically Run Macro After Pivot Table Refresh


    Below is an alternative approach that will detect a pivot change anywhere in the Workbook by being a workbook function. NOTE you need to add this code to the "This Workbook" section and not in a sheet or new module:


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login