VBA Code to Refresh Pivot If One of Two Cell Values Change

trypsin25

New Member
Joined
Jan 7, 2016
Messages
11
Hello! I am attempting to update code to refresh a pivot table if one of two cells values change. I have tried (unsuccessfully) to write an OR statement in the below. I would like to add that if the value changes in cell E6 or C32 the pivot will refresh. Any help appreciated!!!!


VBA Code:
Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = Worksheets("CoExOverview").Range("C32").Address Then
        ActiveSheet.Unprotect Password:="XXXXXX"
        PivotTables("Summary").RefreshTable
        ActiveSheet.Protect Password:="XXXXXX"
        End If
End Sub
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
1,941
I assume that the above code is inserted into the Class Module of worksheet CoExOverview; if it's true then you should use, for example
VBA Code:
Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$C$32" Or Target.Address = "$E$6" Then
        ActiveSheet.Unprotect Password:="XXXXXX"
        PivotTables("Summary").RefreshTable
        ActiveSheet.Protect Password:="XXXXXX"
        End If
End Sub
Bye
 

Watch MrExcel Video

Forum statistics

Threads
1,114,518
Messages
5,548,519
Members
410,843
Latest member
NZJohn
Top