+ Reply to Thread
Results 1 to 4 of 4

Automatic date change in excel sheet print out? Needs fixing

  1. #1
    Registered User
    Join Date
    01-05-2014
    Location
    London, England
    MS-Off Ver
    Excel 20010
    Posts
    28

    Automatic date change in excel sheet print out? Needs fixing

    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.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Automatic date change in excel sheet print out? Needs fixing

    The code is fine, though not well written. But you need to put it into the codemodule of the ThisWorkbook object- in your project explorer, double click the ThisWorkbook object of your workbook "Shift Log and paste the code into the window that appears. To run the code, double click somewhere on the sheet "v2".

    You only need to change this line

    Please Login or Register  to view this content.
    to

    Please Login or Register  to view this content.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    01-05-2014
    Location
    London, England
    MS-Off Ver
    Excel 20010
    Posts
    28

    Re: Automatic date change in excel sheet print out? Needs fixing

    I appreciate your help, the change that you have suggested was used earlier but the code is not displaying the message box and there is no error message. Could you help with a re-write?

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Automatic date change in excel sheet print out? Needs fixing

    Post your workbook - here's my test version. Double click on a cell....

    Print with Dates.xlsm
    Last edited by Bernie Deitrick; 03-29-2014 at 02:25 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel formula for color change and automatic Date Changes
    By ashhm in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-06-2012, 11:07 AM
  2. [SOLVED] Excel 2007 : Automatic Consolidation + Fixing Line Positions
    By pyth_hypo33 in forum Excel General
    Replies: 2
    Last Post: 06-13-2012, 05:24 PM
  3. Change date automatic in an excel column
    By Dahlgren in forum Excel General
    Replies: 2
    Last Post: 04-22-2012, 11:05 PM
  4. fixing a mistake on excel sheet- #REF!
    By pattieayre in forum Excel General
    Replies: 5
    Last Post: 05-26-2008, 02:03 AM
  5. Print Modified date stamp on excel sheet
    By Marc in forum Excel General
    Replies: 1
    Last Post: 08-11-2006, 11:45 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1