locked
Macro to update a pivot table RRS feed

  • Question

  • Evening,

    I would like a code to update pivottable1 on worksheet Causes Lignes when the value in C1 which comes from a dropdown menu is changed.  The machine is under the Prod Activity category which forms the column of the pivot table, the values are a count of Data and the rows are the names of Data.

    I found a version of this code which I have tried to tweak but it doesnt work.  Anyone be able to help me know why?

    Thanks 

    Private Sub Worksheet_Change(ByVal Target As Range)

        If Intersect(Target, Range("C1")) Is Nothing Then Exit Sub
        On Error Resume Next
        Application.EnableEvents = False
        Sheets("Causes Lignes").PivotTables("PivotTable1").PivotCache.Refresh
        With Worksheets("Causes Lignes").PivotTables("PivotTable1")
            .PivotCache.Refresh
            .PivotFields("Prod Activity").CurrentPage = Target.Value
        End With
        Application.EnableEvents = True
    End Sub

    Thursday, January 28, 2016 11:22 PM

Answers

  • Hi Lind89

    Back from the trip. 

    I tested the code, it works fine. Pick a value from dropdown list of the cell "C1" and Pivot table will be updated-filtered accordingly.

    Let me know if you need the file too and I will upload for sharing.

    Glad I could contribute and good luck with your project.


    Cheers

    Gio

    Here we go with the code you need.

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Range("B3")) Is Nothing Then Exit Sub
    
    Dim wsB As Worksheet, wsPT As Worksheet
    Set wsB = ThisWorkbook.Sheets("Base")
    Set wsPT = ThisWorkbook.Sheets("Causes Lignes")
    lr = wsB.Cells(wsB.Rows.Count, "F").End(xlUp).Row
    cf = Application.CountIfs(wsB.Range("F2:F" & lr), wsPT.Range("C1").Value)
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Dim PT As PivotTable, pi As PivotItem, pf As PivotField
    Set PT = ActiveSheet.PivotTables("PivotTable2")
    
    PT.PivotFields("Activite").EnableMultiplePageItems = True
    
    If Not Intersect(Target, Range("C1")) Is Nothing And cf > 0 Then
    Application.EnableEvents = True
    
    PT.PivotCache.MissingItemsLimit = xlMissingItemsNone
    PT.PivotCache.Refresh
    
    For Each pf In PT.PivotFields
        For Each pi In pf.PivotItems
            If pi.Name = piV Then
                If Not pi.Visible Then pi.Visible = True
            End If
        Next pi
    Next pf
    
    For Each pf In PT.PageFields
        pf.CurrentPage = "(All)"
    Next pf
    
    With PT.PivotFields("Activite")
        .ClearAllFilters
    End With
    
    PT.PivotFields("Activite").AutoSort xlAscending, "Activite"
    
    iPF = "Activite"
            Set pf = PT.PivotFields(iPF)
            Z = Range("C1").Value
    For Each pi In PT.PivotFields(iPF).PivotItems
            If pi.Value = "(blank)" Then pi.Visible = False
            If pi.Value = Z Then pi.Visible = True
            If pi.Value <> Z Then pi.Visible = False
    Next pi
    
    ElseIf Not Intersect(Target, Range("C1")) Is Nothing And cf = 0 Then
    Application.EnableEvents = True
    
    PT.PivotCache.MissingItemsLimit = xlMissingItemsNone
    PT.PivotCache.Refresh
    With PT.PivotFields("Activite")
        .ClearAllFilters
    End With
    
    
    End If
    Application.EnableEvents = True
    
    End Sub

    • Proposed as answer by GioPir Sunday, February 21, 2016 10:07 PM
    • Marked as answer by Lind89 Monday, February 22, 2016 11:15 PM
    Sunday, February 21, 2016 10:07 PM

All replies

  • Hi Lind,

    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.
    • Proposed as answer by GioPir Sunday, January 31, 2016 6:38 PM
    • Unproposed as answer by GioPir Thursday, February 4, 2016 9:29 PM
    Friday, January 29, 2016 7:25 AM
  • Hi Lind

    Copy this code in worksheet over your code
    This code will allow to refresh Pivot table anytime C1 is changed

    Best regards 
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Application.ScreenUpdating = False
    
    Dim wsPV As Worksheet
    Set wsPV = ThisWorkbook.Worksheets("Causes Lignes")
    If Not Intersect(Target, wsPV.Range("C1")) Is Nothing Then
    
    Dim PT As PivotTable
    Dim pi As PivotItem
    Dim pf As PivotField
    Set PT = ActiveSheet.PivotTables("PivotTable1")
    
    PT.PivotCache.Refresh
    
    For Each pf In PT.PivotFields
        For Each pi In pf.PivotItems
            If Not pi.Visible Then pi.Visible = True
        Next pi
    Next pf
    
    PT.ClearAllFilters
    Else
    Exit Sub
    End If
    End Sub

    • Proposed as answer by GioPir Sunday, January 31, 2016 6:41 PM
    • Unproposed as answer by GioPir Thursday, February 4, 2016 9:29 PM
    • Proposed as answer by GioPir Thursday, February 4, 2016 9:30 PM
    • Unproposed as answer by GioPir Thursday, February 4, 2016 9:30 PM
    Sunday, January 31, 2016 6:41 PM
  • Hi,

    Sorry but this does not appear to work.  When I change C1 nothing moves in the pivot table.

    Any ideas if i could be missing something?

    Thanks

    Sunday, January 31, 2016 10:37 PM
  • >>>Sorry but this does not appear to work.  When I change C1 nothing moves in the pivot table.

    Any ideas if i could be missing something?<<<

    According to your description, are you certain that "Prod Activity" is a page field?  The CurrentPage property is only applicable to page fields.
    Otherwise I suggest that you could refer to below code:

    Set pField = Worksheets("Causes Lignes").PivotTables("PivotTable1").PivotFields("Prod Activity")
    Do Until pField.PivotFilters.Count = 0
       pField.PivotFilters(1).Delete
    Loop
    Set pFilter = pField.PivotFilters.Add(Type:=xlCaptionEquals, Value1:=Target.Value)

    Monday, February 1, 2016 5:47 AM
  • Hi,

    In the code given by GioPir the field value is not listed so should it matter?

    I tried inserting your code but can't get it work either - not sure I'm putting it in properly.  Could you give me the entire code so I can double check please?

    Thanks for your help.

    Tuesday, February 2, 2016 11:17 PM
  • Hi Lind89

    Could you please share the sample file? 
    Just to get it right, please explain what exactly are you trying to achieve with PT?
    I will try to help finding way out from troubling Pivot :)


    Regards and nice weekend in advance

    GioPir

     
    Thursday, February 4, 2016 9:34 PM
  • Hi,

    Sorry tried to fiddle since but no luck.

    The pivot table lists sources of problems on 15 different production machines.

    The code is simply a way of being able to select only one machine at a time and sort the table from highest ranking to lowest without doing it manually each time.

    I.e. traditionally the table displays all 15 machines.  Manually i use the filter to deselect, then a sort to order.

    Replace these (not so huge i know), operations by a drop down list in C1 that does it automatically after a cell update.

    I guess its just in the parameters that I'm sticking.

    Thanks

    Monday, February 15, 2016 10:40 PM
  • Hi Lind89

    Long time :)
    Not sure if I see whole picture... Lets say:
    "Row labels": are lists of Machines with error codes per machine and "Column labels": responsible staff  

    Then Pivot table will look like this:


    As soon you enter "Machine" code in cell C1 or pick from dropdown list - Pivot table is updated, sorted and filtered  



    If this is your goal then following code will help you to complete your project

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim wsPT As Worksheet
    Set wsPT = ThisWorkbook.Worksheets("MyPivot")
    
    If Not Intersect(Target, wsPT.Range("C1")) Is Nothing Then
    
    Application.ScreenUpdating = False
    'ActiveSheet.Unprotect
    
    Dim PT As PivotTable, pi As PivotItem, pf As PivotField
    Set PT = ActiveSheet.PivotTables("PivotTable1")
    
    PT.ClearAllFilters
    PT.PivotCache.MissingItemsLimit = xlMissingItemsNone
    PT.PivotCache.Refresh
    
    For Each pf In PT.PivotFields
        For Each pi In pf.PivotItems
            If Not pi.Visible Then pi.Visible = True
        Next pi
    Next pf
    
    For Each pf In PT.PageFields
        pf.CurrentPage = "(All)"
    Next pf
    
    PT.PivotFields("Equipment").AutoSort xlAscending, "Equipment"
    PT.PivotFields("ErrorCode").AutoSort xlAscending, "ErrorCode"
    PT.PivotFields("Equipment").ShowDetail = True
            
            iPF = "Equipment"
            Set pf = PT.PivotFields(iPF)
            z = Range("C1").Value
    For Each pi In PT.PivotFields(iPF).PivotItems
       If pi.Value = "(blank)" Then pi.Visible = False
       If pi.Value = z Then pi.Visible = True
       If pi.Value <> z Then pi.Visible = False
    Next pi
            
        PT.PivotSelect "Responsible[All]", xlLabelOnly + xlFirstRow, True
    
    'ActiveSheet.Protect AllowUsingPivotTables:=True
    ActiveWorkbook.ShowPivotTableFieldList = False
    
    End If
    
    End Sub
    Please mark as helpful and in case you need the sample file gladly will share 

    Best regards

    Gio   

    • Proposed as answer by GioPir Wednesday, February 17, 2016 10:25 PM
    • Unproposed as answer by GioPir Sunday, February 21, 2016 10:07 PM
    Wednesday, February 17, 2016 10:24 PM
  • Hi,

    Quicker this time, (hols sorry)!

    Afraid still not for working.  Its me being stupid but I can't see why.

    - Machines (known as Activite) in the columns.  Pivot: Columns = Activite

    - Fault type in the rows.  Pivot : Rows = Fault

    - Number of each fault (count) per machine in the data table.  Pivot : Values = count of Fault

    Heres an example of my table

    Count of Fault Column Labels
    Row Labels CP (blank) MS MC1 LA Grand Total
    A 2 2
    AB 1 1
    B 2 2
    C 1 2 3
    D 1 1
    DE 1 1
    E 2 2
    I 1 1
    S 1 1
    (blank)
    Grand   Total 9 1 3 1 14
    Wednesday, February 17, 2016 10:57 PM
  • Hi Lind

    I'm sure we will make it work :) .  I tried to replicate your PT and tested - it works
    Private Sub Worksheet_activate()
    
    Application.ScreenUpdating = False
    
    ActiveSheet.PivotTables("PivotTable1").PivotCache.MissingItemsLimit = xlMissingItemsNone
        ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    
    Dim PT As PivotTable, pi As PivotItem, pf As PivotField
    Set PT = ActiveSheet.PivotTables("PivotTable1")
    
    
    For Each pf In PT.PivotFields
        For Each pi In pf.PivotItems
            If Not pi.Visible Then pi.Visible = True
        Next pi
    Next pf
    
    For Each pf In PT.PageFields
        pf.CurrentPage = "(All)"
    Next pf
    
    PT.PivotFields("Activity").AutoSort xlAscending, "Activity"
            
            iPF = "Activity"
            Set pf = PT.PivotFields(iPF)
            z = Range("C1").Value
    For Each pi In PT.PivotFields(iPF).PivotItems
       If pi.Value = "(blank)" Then pi.Visible = False
       If pi.Value = z Then pi.Visible = True
       If pi.Value <> z Then pi.Visible = False
    Next pi
            
    PT.PivotSelect "Fuel[All]", xlLabelOnly + xlFirstRow, True
    ActiveWorkbook.ShowPivotTableFieldList = False
    
    End Sub
    If you sill face difficulties adopting code, then share a sample file and I will send you back working one

    Cheers

    Gio
    Thursday, February 18, 2016 2:11 PM
  • Hi,

    Stil not for working.

    Heres a link to the file through dropbox - https://www.dropbox.com/s/qn2mjbe3pn4yrcf/Test%20File.xlsm?dl=0.

    Let me know if you have problems getting in or understanding it.

    Thanks for your help - again.

    Friday, February 19, 2016 9:32 PM
  • Hi Lind89

    Back from the trip. 

    I tested the code, it works fine. Pick a value from dropdown list of the cell "C1" and Pivot table will be updated-filtered accordingly.

    Let me know if you need the file too and I will upload for sharing.

    Glad I could contribute and good luck with your project.


    Cheers

    Gio

    Here we go with the code you need.

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Range("B3")) Is Nothing Then Exit Sub
    
    Dim wsB As Worksheet, wsPT As Worksheet
    Set wsB = ThisWorkbook.Sheets("Base")
    Set wsPT = ThisWorkbook.Sheets("Causes Lignes")
    lr = wsB.Cells(wsB.Rows.Count, "F").End(xlUp).Row
    cf = Application.CountIfs(wsB.Range("F2:F" & lr), wsPT.Range("C1").Value)
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Dim PT As PivotTable, pi As PivotItem, pf As PivotField
    Set PT = ActiveSheet.PivotTables("PivotTable2")
    
    PT.PivotFields("Activite").EnableMultiplePageItems = True
    
    If Not Intersect(Target, Range("C1")) Is Nothing And cf > 0 Then
    Application.EnableEvents = True
    
    PT.PivotCache.MissingItemsLimit = xlMissingItemsNone
    PT.PivotCache.Refresh
    
    For Each pf In PT.PivotFields
        For Each pi In pf.PivotItems
            If pi.Name = piV Then
                If Not pi.Visible Then pi.Visible = True
            End If
        Next pi
    Next pf
    
    For Each pf In PT.PageFields
        pf.CurrentPage = "(All)"
    Next pf
    
    With PT.PivotFields("Activite")
        .ClearAllFilters
    End With
    
    PT.PivotFields("Activite").AutoSort xlAscending, "Activite"
    
    iPF = "Activite"
            Set pf = PT.PivotFields(iPF)
            Z = Range("C1").Value
    For Each pi In PT.PivotFields(iPF).PivotItems
            If pi.Value = "(blank)" Then pi.Visible = False
            If pi.Value = Z Then pi.Visible = True
            If pi.Value <> Z Then pi.Visible = False
    Next pi
    
    ElseIf Not Intersect(Target, Range("C1")) Is Nothing And cf = 0 Then
    Application.EnableEvents = True
    
    PT.PivotCache.MissingItemsLimit = xlMissingItemsNone
    PT.PivotCache.Refresh
    With PT.PivotFields("Activite")
        .ClearAllFilters
    End With
    
    
    End If
    Application.EnableEvents = True
    
    End Sub

    • Proposed as answer by GioPir Sunday, February 21, 2016 10:07 PM
    • Marked as answer by Lind89 Monday, February 22, 2016 11:15 PM
    Sunday, February 21, 2016 10:07 PM
  • Perfect.

    Thanks a million for all the help.

    Monday, February 22, 2016 11:15 PM