vba code to run on different sheet when active worksheet cell change

[Zimmerman]

ok so I have this macro that works perfectly fine when I'm on this sheet. This is in a sheet that is called "RL". The issue I have is that the cells in the columns 12-19 on sheet "RL" are a result of a lookup from a pivot table. So they are formulas. So when the pivot table (which is a sheet named "Pivot Table" in the same workbook) gets updated this code doesn't actually run because this code only runs when it's the active sheet. So I guess the question is how can I have sheet "RL" run this code when a different sheet is the active sheet.

 Dim WasValue
 Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 12 Then Cells(Target.Row, 21).Value = WasValue
 If Target.Column = 13 Then Cells(Target.Row, 22).Value = WasValue
 If Target.Column = 14 Then Cells(Target.Row, 23).Value = WasValue
 If Target.Column = 15 Then Cells(Target.Row, 24).Value = WasValue
 If Target.Column = 16 Then Cells(Target.Row, 25).Value = WasValue
 If Target.Column = 17 Then Cells(Target.Row, 26).Value = WasValue
 If Target.Column = 18 Then Cells(Target.Row, 27).Value = WasValue
 If Target.Column = 19 Then Cells(Target.Row, 28).Value = WasValue
 End Sub

 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Target.Column = 12 Then WasValue = Target.Value
 If Target.Column = 13 Then WasValue = Target.Value
 If Target.Column = 14 Then WasValue = Target.Value
 If Target.Column = 15 Then WasValue = Target.Value
 If Target.Column = 16 Then WasValue = Target.Value
 If Target.Column = 17 Then WasValue = Target.Value
 If Target.Column = 18 Then WasValue = Target.Value
 If Target.Column = 19 Then WasValue = Target.Value
 End Sub

Thanks in advance
excel 2016

 

[Tim_Excel_]

You'll have to move this to the sheet module that the pivot table is on. Then, refer to the RL sheet to have the changes happen on the correct sheet:

With Worksheets("RL")
If Target.Column = 12 Then .Cells(Target.Row, 21).Value = WasValue
End With

 

[Joe4]

A little education:

Worksheet events only apply to the sheet that they are placed in (the specific sheet module).
The Worksheet_Change event is only triggered when a cell on that sheet is manually changed (changes in values returned by formulas are NOT caught).
The Worksheet_SelectionChange event is only triggered when a cell on that sheet is selected (catches moves to specified cells).
The Worksheet_Calculate event is triggered by changes in calculated values on that sheet. So a formula value that changes will trigger it. However, it cannot identify which cell was changed, only that a cell on the sheet was changed (which is why this event does not have a "Target" argument).

Here is a listing of the different Worksheet events: https://msdn.microsoft.com/en-us/VBA/Excel-VBA/articles/worksheet-object-excel#Anchor_2

 

[Zimmerman]

Thanks for the help and the info on what triggers what. Much appreciated. So I guess I'm wondering if there's any other way to capture a cell value before from a pivot table before it gets dated on a different sheet.

 

[Joe4]

That seems rather tricky, since you are trying to get the before value of a calculated field on a different sheet.
You could use Tim's approach to capture the changes on the other sheet, but depending on your formula, and the correlation between the two, it may be hard to translate it over to the other sheet.

It may be that you want this event procedure code to capture the before/after values on the pivot table sheet where they are being manually updated, and then look up the new value on your other sheet, then update the appropriate column with the previous value. That is about the only thing I can think of.