At work I have a register/log. Its just a printout of a standard format in excel sheet. I print a month of sheets at a time and write the day and date on it by hand at the top. Is there some formula in excel by which it prints the dates as well in a progressive manner. Eg. If today is 29th May 2014 and today I print 30 copies of the register/log. The first copy has todays date on it and the the next one had tomorrows date....and the 30th copy automatically has the end of April 2014 printed on it.
It would be a big help if some advanced excel/VBA user can help me out as theres quite a few such documents where I can use this formula.
I've found this code but cannot get it to work.
My workbook is called Shift Log, the worksheet is called v2 and I'm using Excel from MS Office Professional Plus 2010 (32bit)
#Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)
Dim sDate, i
retryDate:
sDate = InputBox("Enter the starting date, or click 'OK'" & _
" for the current date", "Start Date")
If sDate = "" Then
sDate = Date
ElseIf Not IsDate(sDate) Then
retryDate = MsgBox("Invalid date format", vbRetryCancel + vbCritical, "Retry?")
Select Case retry
Case Is = vbRetry
GoTo retryDate
Case Is = vbCancel
Target.Offset(0, 1).Select
Exit Sub
End Select
End If
retryNum:
numCopies = InputBox("Enter the number of signup " & _
"sheets to print.", "Days to Print")
If numCopies = "" Then
Target.Offset(0, 1).Select
Exit Sub
ElseIf Not IsNumeric(numCopies) Then
retryNum = MsgBox("Invalid numeric format", vbRetryCancel + vbCritical, "Retry?")
Select Case retryNum
Case Is = vbRetry
GoTo retryNum
Case Is = vbCancel
Target.Offset(0, 1).Select
Exit Sub
End Select
End If
For i = 0 To numCopies - 1
ActiveSheet.Range("A1").Value = CDate(sDate) + i 'I named my date cell as PageDate as the date is within a number of merged cells
ActiveSheet.PrintOut copies:=1
Next i
Target.Offset(0, 1).Select
End Sub#
Thanks in advance.
Bookmarks