VBA to track History changes from a worksheet to a new worksheet

[dade652003]

Hello

I need help and not able to find my solution.

I have in a sheet called TOC on cell J33, which updates every 2 minutes based on a = function to another sheet that uses a api query.

My goal is to track last value (history) before it updates, and have the data output on another sheet called Pro, going down the cell startign with cell A1.

Example:
Sheet: TOC
Cell: J33
Contains value $500, and updated to $1000, then updated $400...

Output:
Sheet: Pro
Cell A1 500
Cell A2 1000
Cell A4 400
....


Could this be done?

Amy information would be greatly be appreciated.

Thank you

 

[JLGWhiz]

You can try this in the sheet TOC code module.

Private Sub Worksheet_Calculate()
Sheets("Pro").Cells(Rows.Count, 1).End(xlUp)(2) = Sheets("TOC").Range("J33").Value
End Sub

I don't think Worksheet_Change would work because it ignores values change by calculation, which I think the formula in J33 would be seen as by Excel. It won't hurt to try it. If it does not work, just delete it.