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

 

[njimack]

Welcome to the board. Something like this?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Last_Row As Long
If Target.Address = "$D$5" Then
    
    With Sheets("Sheet2")
        Last_Row = .Range("D" & Rows.Count).End(xlUp).Offset(1).Row
        .Cells(Last_Row, 4) = Sheets("Sheet1").Range("D5").Value
        .Cells(Last_Row, 5) = Now
    End With
End If
    

End Sub

 

[bharath.blitz]

That was real quick. Thanks Neil
-----------

I entered your code in sheet 1 and entered data in D5, but nothing appeared in sheet 2?? What am i doing wrong

Please pardon me, I am amateur in macros, I am building an inventory tool where this is required.

 

[njimack]

Did you definitely paste the code into the module of the sheet that you will be entering data into D5?

 

[bharath.blitz]

Yep...I did a right click on sheet 1 and clicked on view code, pasted code and closed VB window

 

[njimack]

Very strange...it works perfectly for me. Do you have events enabled?

 

[bharath.blitz]

Nope..Should it be included in your code??

 

[njimack]

No, the code won't fire unless events are enabled. From the VBE, press Ctrl+G to display the Immediate window. Then enter the below code:
Application.EnableEvents = True

 

[bharath.blitz]

Done but still . Can you please PM me the dummy excel with that code...let me have a look at it. Thank a lot for your help.

 

[njimack]

If you PM me your email address I'll send you the file.