Hi Howard,

Am Sun, 7 Aug 2016 02:22:59 -0700 (PDT) schrieb L. Howard:

Indeed. That appears to be the case, and the cell is one column removed from the formula. Turns out no VLOOKUP but a long, long IF/OR formula. I am using a VLOOKUP and referring to the lookup_value cell (where the change takes place)and that looks to be doing the trick.

or try it this way:

Private Sub Worksheet_Calculate()
Dim varData As Variant
Dim i As Long

varData = Range("B8:F37")
For i = LBound(varData) To UBound(varData)
If varData(i, 1) = "Cash" Then
Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp)(2) _
.Resize(1, UBound(varData, 2)).Value =
Application.Index(varData, i, 0)
ElseIf varData(i, 1) = "Equity" Then
Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp)(2) _
.Resize(1, UBound(varData, 2)).Value =
Application.Index(varData, i, 0)
End If
Next

End Sub

Workbook_Calculate doesn't work with target.


Regards
Claus B.
--
Windows10
Office 2016