|I believe that these 2 sets of code will do what you asked for. However, there is one big piece missing. There is nothing here that will add the current date's new data to the historical data.|
Since there are so many variables involved with inputting the data, I didn't want to waste time writing code that won't do what you want.
For example, when do you want the data copied to the historical data - as soon as the last piece of data is entered into D1? Just before the workbook is closed? Just before the workbook is saved? Do you want to click a button to save today's data? etc.
Let's say you enter today's data and then decide to search for another day's data. Would you want to be able to pull today's data back again during that same session?
Would you want to be able to change today's data assuming it's still "today"?
Until it's clear how you want to deal with the new (current day's) data, it's tough to offer any suggestions.
As far as your other 2 requirements, try this in a backup copy of your workbook. This code writes data and deletes data, so you don't want to test this your only copy of your workbook since it cannot be undone.
Note: This code makes 3 assumptions:
1 - The data entry cells you mentioned in your post are correct, i.e. A1:D1.
2 - The data entry cells are on Sheet 1.
3 - The historical data is stored in Sheet 2 Columns A:D.
You will have to modify the code if it doesn't fit your workbook layout.
Right click on the sheet tab for Sheet 1.
Paste this code into the pane that pane that opens.
When you enter a date in Sheet1!A1, it will lookup that date in Sheet2 Column A and pull the data from columns B:D. If today's date is entered, it won't do anything (for now) so that you can enter the new data in Sheet1!B1:D1.
Private Sub Worksheet_Change(ByVal Target As Range)
'Check to see if a change was made to A1
If Target.Address = "$A$1" Then
'Determine last Row with data in Sheet2
lastRw = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row
'Look up date in Sheet2 and pull data
With Sheets(2).Range("A1:A" & lastRw)
Set d = .Find(Target)
If Not d Is Nothing Then
Application.EnableEvents = False
For colNum = 2 To 4
Cells(1, colNum) = .Cells(d.Row, colNum)
Application.EnableEvents = True
'Message for Invalid Date
If Target <> Date Then
MsgBox "No Data for Date Requested"
Edit: See Response # 3 for an updated suggestion for the following code.
While still in the VBA editor, Double click the ThisWorkbook module for the workbook. Paste this code into the pane that opens. This is the code that will clear Sheet1!A1:D1 each time the workbook is opened.
Private Sub Workbook_Open()
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.