track cell value changes and date of change within the same sheet

[BoydRed]

I have a workbook where I have several sheets. Each sheet pulls a value from the first sheet and inputs it into a specific cell (e19) in each of the remaining sheets ,via a simple = formula. the value changes every time the macros run via a button push in the first sheet. I'd like to record the history of (e19)with the date it changed and input into a running list in columns "a" and "b" of the same respective sheet, rather than a separate history sheet. How do i do that?

Attached is a copy of my workbook.

Thanks in advance for the help.

[LJMetzger]

Hi BoydRed,

I hope I understood your question correctly. My solution puts the date and time (you can change the date and time to date only or whatever format you want) in Column A and the Value in Column B for whichever day is the day being used.

To do this I:
a. Saved the existing 'E19' values when the workbook opens using Workbook_Open() in Module 'ThisWorkbook'.
b. Accessed the event each time the value of 'E19' changed (formula calculation) using Worksheet_Calculate() in each of the 14 'Daily tracker' modules.
c. Wrote the history to the proper 'Daily tracker' sheet using AddHistoryToCurrentDailyTrackerSheet() in Module 'ModHistory'.

The attached workbook is in Excel 2007 format (because Excel 2003 format in which my workbook was developed created an output file that was TOO BIG). You may have to copy the Macros to your workbook, because some of the Excel formatting may have been lost by Excel 2003. If the formatting is OK, then my workbook should work with your version of Excel.

Lewis

ThisWorkbook Module:

Please Login or Register  to view this content.

Identical Code in each of the 14 'Daily tracker' sheet Modules:

Please Login or Register  to view this content.

ModHistory Module:

Please Login or Register  to view this content.