locked
A macro that updates sheet when a pivot table is updated RRS feed

  • Question

  • Hello,

    I have a workbook that has a sheet with two pivot tables. I also have another sheet that references these pivot table so there's GETPIVOTDATA formulas. Is it possible to have a macro that will fill down the whenever the pivot tables get updated?

    Thank you

    Monday, June 20, 2016 1:01 PM

Answers

  • Zaag,

    You will need to create a worksheet event in the worksheet code module that your pivot table is housed on.  Here is an example of how this might work.  Please let me know if you have any questions:

    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)    
    
        Dim LastRow As Long        
    
        MsgBox "This event fires when a pivot table on your respective sheet is updated....", vbInformation, "Event Fire Test"        
    
        'Maybe try some code like this......    
        'LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
        'ActiveSheet.Range("A1:A" & LastRow) = "=GETPIVOTDATA(YOUR FORMULA DATA HERE)"
    
    End Sub




    Matt Mickle

    • Marked as answer by David_JunFeng Wednesday, June 29, 2016 9:56 AM
    Tuesday, June 21, 2016 1:11 PM

All replies

  • Hi zaag,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,

    Emi Zhang
    TechNet Community Support


    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Tuesday, June 21, 2016 6:09 AM
  • Zaag,

    You will need to create a worksheet event in the worksheet code module that your pivot table is housed on.  Here is an example of how this might work.  Please let me know if you have any questions:

    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)    
    
        Dim LastRow As Long        
    
        MsgBox "This event fires when a pivot table on your respective sheet is updated....", vbInformation, "Event Fire Test"        
    
        'Maybe try some code like this......    
        'LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
        'ActiveSheet.Range("A1:A" & LastRow) = "=GETPIVOTDATA(YOUR FORMULA DATA HERE)"
    
    End Sub




    Matt Mickle

    • Marked as answer by David_JunFeng Wednesday, June 29, 2016 9:56 AM
    Tuesday, June 21, 2016 1:11 PM