Excel: Macro that will display data based on a inputted date

January 29, 2012 at 10:46:59
Specs: Windows Vista

New to VB and would appreciate some help with the following.

I am trying to create a sheet where by i can input my capacity/demand on any new day. However, i want to be able to store the historic data and be able to bring it up again by inputting a date for a specific days data that i wish to view.

Lets say that A1 is the cell where a date can be inputted.

B1 is where i input todays capacity
C1 is where i input todays demand
D1 is where i input expected WIP

When i open up the document tomorrow, i would love these fields to be blank ready to be populated. However, i would like to be able to view yesterdays data by inputting yesterdays date in A1. If the date is not a valid date i.e no data was recorded for that day, a message box could pop up saying 'not a valid date'.

Can anyone help?

See More: Excel: Macro that will display data based on a inputted date

Report •

January 29, 2012 at 13:55:24
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"
           End If
         End If
       End With
   End If
End Sub

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()
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

January 29, 2012 at 14:35:40

Thanks very much for getting back to me.

In answer to your question, i would like it to save todays data when you save the document.

Thanks again

Report •

January 29, 2012 at 17:14:18
In that case, use this code in the ThisWorkbook module instead of the WorkbookOpen code offered above.

I've edited my previous post to reflect that.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Check for Today's date
  If Sheets(1).Range("A1") = Date Then
'Find Next empty Row in Sheet 2
    nxtRw = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
'Copy Today's data to next row in Sheet 2
    Sheets(1).Range("A1:D1").Copy Destination:=Sheets(2).Range("A" & nxtRw)
  End If
'Clear input cells
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

January 30, 2012 at 20:00:25
I think a database would be more effective for this application.

Report •

Ask Question