Ken,
Here is some code that will take care of what you want. The first code (WorkBook_Open) goes in the ThisWorkbook module, and will execute whenever the workbook is open. If the last date entered is not todays date, then it will enter todays date in the next column. The next code (FindDate) goes in a normal module and will take you to the date that you enter in an input box. You can modify the code how ever you need it. If you need somemore help let me know. Hope this helps. Let me know!
Ryan
' This assumes the dates are in column A
Private Sub Workbook_Open()
Dim LastRow As Integer
LastRow = Range("A65536").End(xlUp).Row
If Cells(LastRow, 1).Value <> Date Then Cells(LastRow + 1, 1).Value = Date
End Sub
' This brings up an input box to enter a date into and then takes you to that date.
Sub FindDate()
Dim FindDate As Date
Dim x As Integer
On Error GoTo Handler
FindDate = InputBox("Enter date", "Date")
x = 1
Do While Cells(x, 1).Value <> FindDate
x = x + 1
CellRow = x
Loop
Rows(CellRow).Select
Exit Sub
Handler:
MsgBox "Unable to find date", vbCritical, "Error"
End Sub
Ken,
I said that the code will put todays date in the next column, I meant the next row, like you wanted it. Sorry about that.
Ryan
Ken,
Here you go, works like a charm, let me know!
Ryan
Private Sub Workbook_Open()
Dim LastRow As Integer
Dim Done As Boolean
Done = False
Do While Done = False
LastRow = Range("A65536").End(xlUp).Row
If Cells(LastRow, 1).Value = "" Then
Cells(LastRow, 1).Value = Date
Exit Sub
End If
If Cells(LastRow,1).Value > Date Then Exit Sub
If Cells(LastRow, 1).Value = Date Then Exit Sub
If Cells(LastRow, 1).Value = Date - 1 Then
Cells(LastRow + 1, 1).Value = Date
Done = True
Else
Cells(LastRow + 1, 1).Value = Cells(LastRow, 1).Value + 1
Done = False
End If
Loop
End Sub
Let me know what you need!
Ryan
What about if it wan not open for a few days though and i want those dates too
and i want only certain sheets to do this.
That was nice. You want to make it skip weekends and hlidays too? and add a few formula's to the other rows. And only do it to certain sheets? Or do I have to do some of the work myself?
Thanks for the help. It works great.