Clear values in cell range when updating pivot table

[nflami]

Hello all,

I am trying to create a macro using VBA that will automatically run when I update a report filter in my pivot table. My pivot table consists of a bunch of my companies item numbers and the corresponding sales by month. I review our items daily and would like to create a macro that clears values in a range that are then run through some calculations off to the side of the pivot table when I change the displayed item in the report filter.

Currently I am using a macro with a button named "cleardin" to do this for me, but at times I forget to run it and it throws off my final calculation, so I was hoping to create a macro that runs automatically when I change the value in the pivot’s report filter.

I have tried using the below code as an example for my needs, referencing the cell in the report filter as the trigger to clear the values in the range, but haven't had any success as I am very new to VBA.

Private</SPAN> Sub</SPAN> Worksheet_Change(ByVal</SPAN> Target As</SPAN> Range)
    With</SPAN> Target
        If</SPAN> .Address = "$b$1" Then</SPAN>
            Range("a12:h500").Value = ""
        End</SPAN> If</SPAN>
    End With</SPAN>
End Sub</SPAN>

Any help would be much appreciated, thank you!</SPAN>

 

[Jerry Sullivan]

Hi and Welcome to MrExcel,

The reason that isn't working as you expect is that when the PivotTable report filter is changed, Target is a reference to all the cells in the PivotTable that were updated- not just the cell with the report filter.

A technique you can use to diagnose this is to add a Debug.Print statement to your code which will send useful information to the Immediate Window of the VB Editor. If your Immediate Window isn't already displayed, you can key Ctrl-G to show it.

Private Sub Worksheet_Change(ByVal Target As Range)
[COLOR="#0000CD"]   Debug.Print "Changed range: " & Target.Address[/COLOR]
 
   With Target
        If .Address = "$b$1" Then
            Range("a12:h500").Value = ""
        End If
    End With
End Sub

If you want to clear a range whenever the PivotTable is updated, you can do that through the Worksheet_PivotTableUpdate event.

If you only want to clear that range when a specific ReportFilter has been changed, that's a bit more difficult. For that, I'd suggest storing the current value of the ReportFilter for a specific field in a Cell or a defined Name.

Whenever the Worksheet_PivotTableUpdate event is triggered, compare the current value of the ReportFilter with the stored value. If it doesn't match, run your cleardin process, and store the "new" current value of the ReportFilter.

Just ask if you want some help with coding that.

 

[nflami]

Thanks for the help.

I took your advice and used the Worksheet_PivotTableUpdate event, which will work great because I would like the range to clear anytime the pivot table is updated, I started simple just to see if I could get the event to kick off since I will need help coding the macro correctly.

The code I used was:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
 
 MsgBox "Remember to Clear Values."
 
End Sub

Unfortunately I could not get to work in my workbook, and I could not diagnose the problem. I have three versions the workbook (3 different file names) in this format and none of them are responding to the update event. Although I have tried it in other workbooks and it worked fine. Maybe the problem is because there are already macros in this workbook and the workbooks the event worked correctly in did not have any VBA code prior?

Back to the code for PivotTableUpdate event, the range I would like to clear it is "D3:AM3" which will not change. Or to run the macro named "cleardin".

Thanks again for the help!</SPAN>

 

[nflami]

Jerry,

Thanks for your help. The problem was I had multiple VBA modules with "Private Sub" coded throughout. So after I deleted all the duplicate code everything worked fine.
Then I entered the simple code:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Application.Run "cleardin"
End Sub

Now when I update the pivot table my macro kicks off. Thanks!