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
> 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.
>
>
|
Similar Threads
