How can I make a sheet display data from another sheet based on date criteria?

[CW]

Hello,

Say I have an Excel workbook with 3 sheets (sheet1, sheet2, sheet3).

Each sheet has two columns (Date, Thing that happened on that date).

I would like to know if there's a way to have a fourth sheet, called
"on this date in history" (or whatever). It would consider today's
date and display each thing that happened on todays date in previous
years on each row.

So basically, if I open the workbook on May 11th, 2007 and go to the
fourth sheet, it would show me a list list of events that happened on
May 11th in previous years, and it would update for each day that I
view it. Then when I add rows to sheets 1, 2, and 3, it would update
on the fourth sheet.

Furthermore, it would be great if there could be a third column on
the "on this date in history sheet" that displays the name of the
sheet that the data in each row was taken from.

I understand that this would require the use of an Auto_Open macro
and some VB code. Any help on this would be greatly appreciated!
Thank you.

 

[Guest]

In the 4th Sheet in Cell A1 type =TODAY()
In Cell A2 type Date
Set up a Worksheet Activate Event under VBA
Code could be as followed. I do not know the ranges or worksheet names so
you'll have to insert those yourself.


Dim wb As Worksheet

For Each wb in Worksheets

If Worksheet.Name = "Sheet4" Then
End Sub
Else

Range("A1").Activate
Do Until ActiveCell.Value = ""
If ActiveCell.Value = Sheets("Sheet4").Range("A1").Value Then
ActiveCell.Offset(0,1).Copy
Sheets("Sheet4").Select
Range("A1").End(xlDown).Offset(1,0).PasteSpecial
Else Endif

Loop

Next wb

EndSub

I haven't really tested it, but if you understand VBA well you can solve
your way around the problems and get it to work. Basically its saying each
time you enter Sheet 4 to look through Sheets 1 through 3 for Todays date,
then copy the cell next to the date and paste it in to Sheet 4. Let me know