VBA to update formula when a value changes in a refence range

[Dokat]

Hi,

I have an Excel formula that gives me the last Friday's date "=TODAY()-WEEKDAY(TODAY())-1" in cell A1

I want to update cell A1 only when the values in reference range in another worksheet B2:D469 changes.

Below is the code i am using but the issue is code only works when i manually make a change in the range. However values in the range gets updated when source pivot table refresh. I want code to get updated when i refresh the pivot table and the values in the range "B2:D469" changes.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

Set KeyCells = Worksheets.("Source").Range("B2:D469")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then

Worksheets.("Dashboard").Range ("A1").EnableCalculation = True

End If
End Sub

 

[PCosta87]

Hi,

I have an Excel formula that gives me the last Friday's date "=TODAY()-WEEKDAY(TODAY())-1" in cell A1

I want to update cell A1 only when the values in reference range in another worksheet B2:D469 changes.

Below is the code i am using but the issue is code only works when i manually make a change in the range. However values in the range gets updated when source pivot table refresh. I want code to get updated when i refresh the pivot table and the values in the range "B2:D469" changes.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

Set KeyCells = Worksheets.("Source").Range("B2:D469")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then

Worksheets.("Dashboard").Range ("A1").EnableCalculation = True

End If
End Sub

Hi,

Have you tried triggering with PivotTableUpdate worksheet event?

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

End Sub