Automating a Ctrl+ Recorded macro

[ryanduck]

I'm using a ctrl+r (r in this case) recorded macro to refresh a auto-filter every time I filter a pivot table using page fields.

Ideally instead of hitting ctrl+r to start the recorded macro, I would like to have it happen automatically every time I filter the pivot table using the page fields...

Any ideas?

 

[wigi]

Hi

This is possible I think - I did not test it.

Try to use the

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

End Sub

event in the correct sheet. You can add the code if you right-click that worksheet, and paste the code above in the white canvas. Then between the 2 lines of codes, add the name of the procedure to be executed.

Can you try it and give feedback please?

Wigi

 

[ryanduck]

This did not work:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Selection.AutoFilter Field:=1, Criteria1:=">0", Operator:=xlOr, _
        Criteria2:="=*"
End Sub

Hi

This is possible I think - I did not test it.

Try to use the

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
 
End Sub

event in the correct sheet. You can add the code if you right-click that worksheet, and paste the code above in the white canvas. Then between the 2 lines of codes, add the name of the procedure to be executed.

Can you try it and give feedback please?

Wigi

 

[ryanduck]

anyone else take a stab at it?

 

[deletedalien]

i KNOW that this works (it does for me)

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim iSect As Range
    Set iSect = Application.Intersect(Target, Range("b4"))
    If Not iSect Is Nothing Then
        Call PivotMacro
    End If
End Sub

it workes because i have a target range set to B4 which is the filter that i always use on my pivot, however im pretty sure you can set a range of cells to be the target.

HTH

 

[Marcelo Branco]

Hi,

Following wigi's suggestion, try this

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Call MyMacro
End Sub

Use the name of your macro instead of MyMacro.
(maybe in your macro you have to specify the range to be filtered instead of Selection)

HTH

M.