PDA

View Full Version : Solved: Enter to days date on opening correct sheet



BENSON
12-13-2007, 10:10 PM
I have a workbook with seven sheets labeled "TUES,WED,THURS,FRI,SAT,SUN,MON" on opening this work book I would like to have todays date appear in collum A in the first empty row in the respective worksheet. IE: if the date was 14th Dec the date would appear in the first empty row in the worksheet labeled "FRI"

Thanks for any help

anandbohra
12-13-2007, 11:10 PM
paste above code in your This workbook


Private Sub Workbook_Open()
Dim a
a = Weekday(Date)
Select Case a
Case 1
Sheets("SUN").Range("A65536").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).FormulaR1C1 = Date
Case 2
Sheets("MON").Range("A65536").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).FormulaR1C1 = Date
Case 3
Sheets("TUES").Range("A65536").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).FormulaR1C1 = Date
Case 4
Sheets("WED").Range("A65536").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).FormulaR1C1 = Date
Case 5
Sheets("THURS").Range("A65536").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).FormulaR1C1 = Date
Case 6
Sheets("FRI").Range("A65536").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).FormulaR1C1 = Date
Case 7
Sheets("SAT").Range("A65536").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).FormulaR1C1 = Date

End Select

End Sub

BENSON
12-13-2007, 11:18 PM
PERFECT MANY THANKS

Bob Phillips
12-14-2007, 02:35 AM
An alternative



Private Sub Workbook_Open()
With Worksheets(Format(Weekday(Date), "ddd"))
.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Date
End With
End Sub