Data entry to different cell when date changes

[bharath.blitz]

Hi Experts,

My problem is interesting and i need your expert help on that. My issue

1) When i enter data (number) in D5 in Sheet 1, I want that data to appear in another sheet (D5 in Sheet2) with date and time in adjacent cells - This i was able to acheive through macros

2) Now issue is when i open the excel tomorrow and enter new data in D5 in sheet 1, I want this number to appear in D6-Sheet 2, that is one cell below yesterdays data, without overwriting yesterday's data

3) Like this when i change data in D5 Sheet1 everyday, the data should get stored in sheet 2, everyday in onc cell below previous day's data. So that when i go to sheet 2, i can see a months data in one place

Is it complex to acheive this?? I am stuck here. Experts please help

 

[bharath.blitz]

Neil...I found why it is not working...makes it more complex

Cell D5 receives its data from another sheet

1) I enter data in sheet 3
2) Cell D5 has a formula which refers to data in sheet 3 and calculates value
3) Now this value has to be recorded in sheet 2 as your code does

---When i remove formula from D5 and enter data manually code works perfect

when D5 calculates data from formula, this code does not work

---any idea??

 

[njimack]

This should do it!

Private Sub Worksheet_Calculate()
Dim Last_Row As Long
Dim x
x = Range("D5").Value
    
    With Sheets("Sheet2")
        Last_Row = .Range("D" & Rows.Count).End(xlUp).Offset(1).Row
        If .Cells(Last_Row, 4) <> x Then
            .Cells(Last_Row, 4) = Sheets("Sheet1").Range("D5").Value
            .Cells(Last_Row, 5) = Now
        End If
    End With

End Sub

 

[bharath.blitz]

awesome...works perfect...thanks a zillion good learning for me