Results 1 to 6 of 6

Thread: Auto Change Date When Printing

  1. #1

    Auto Change Date When Printing



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    Basically what I'm after is finding out how to press print and then every sheet that is printed has the date changed automatically.

    So basically if I press 31 prints, the 1st print will be the 1st Jan the 2nd print the 2nd etc etc.

    is this possible? or do I have to manually change the date section and print off the whole year one by one?

    Thanks for your time.

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    792
    Articles
    0
    Excel Version
    2010
    Try This:

    Code:
    Sub DateEachCopy()
        Dim CopyCount As Long
        Dim CopyNo As Integer
        Dim S As Integer
        CopyCount = Application.InputBox("How many days are we printing ?", Type:=1)
        S = Application.InputBox("Which day do we start with ?", Type:=1)
        For CopyNo = 1 To CopyCount
            With ActiveSheet
            .PageSetup.RightFooter = "&""Arial,Bold""&14DATE : " & S & Format(Now, " MMMM YYYY")
            .PrintOut
             End With
            S = S + 1
        Next CopyNo
    ActiveSheet.PageSetup.RightFooter = ""
    End Sub

  3. #3
    Quote Originally Posted by Hercules1946 View Post
    Try This:

    Code:
    Sub DateEachCopy()
        Dim CopyCount As Long
        Dim CopyNo As Integer
        Dim S As Integer
        CopyCount = Application.InputBox("How many days are we printing ?", Type:=1)
        S = Application.InputBox("Which day do we start with ?", Type:=1)
        For CopyNo = 1 To CopyCount
            With ActiveSheet
            .PageSetup.RightFooter = "&""Arial,Bold""&14DATE : " & S & Format(Now, " MMMM YYYY")
            .PrintOut
             End With
            S = S + 1
        Next CopyNo
    ActiveSheet.PageSetup.RightFooter = ""
    End Sub
    Thanks mate, sorry for being completely useless, but what do I do with the code? Do I open some sort of complex editor?

  4. #4
    Quote Originally Posted by Hercules1946 View Post
    Try This:

    Code:
    Sub DateEachCopy()
        Dim CopyCount As Long
        Dim CopyNo As Integer
        Dim S As Integer
        CopyCount = Application.InputBox("How many days are we printing ?", Type:=1)
        S = Application.InputBox("Which day do we start with ?", Type:=1)
        For CopyNo = 1 To CopyCount
            With ActiveSheet
            .PageSetup.RightFooter = "&""Arial,Bold""&14DATE : " & S & Format(Now, " MMMM YYYY")
            .PrintOut
             End With
            S = S + 1
        Next CopyNo
    ActiveSheet.PageSetup.RightFooter = ""
    End Sub
    Thanks for this mate, figured out the input on running macro etc, any ideas how I can move the position of the date to where I want or is it all predefined to up down left right etc.

  5. #5
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    792
    Articles
    0
    Excel Version
    2010
    Hello
    No it isn't complex at all. Just do the following:
    1. Open your workbook in Excel
    2. Click Alt + F11 to open the Visual Basic Editor
    3. Click Ctrl + R to ensure that the VBA Project Window is Open
    4. On the Menu Bar, Click Insert -->Module. An additional entry called Modulex will appear in the project window (where x is the next available number from 1 upwards).
    A code window will open and the cursor will be placed in the top left corner, ready to accept your code.
    5. Highlight all of the code in the code window of the last post, right click and "copy". Return to your code window, right click at the cursor point, and "Paste".
    6. The code as above should be listed in the code window, and the macro name will appear in the top right box.
    7. Click Alt + Q to close the Editor and return to Excel. Make sure that the Active Sheet is the one you are printing from, and the print range and page settings are selected correctly.
    8. Click Alt + F8 to display the Macro Window. If "DateEachCopy" isn't selected, select it; Click "Run" and follow the screen prompts.

    HTH

  6. #6
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    792
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by sal1tech View Post
    Thanks for this mate, figured out the input on running macro etc, any ideas how I can move the position of the date to where I want or is it all predefined to up down left right etc.
    You will see that the code mentions .RightFooter in two places.

    1. To change the position of the footer (or header) change Right to Center or Left.
    2. You can also change Footer to Header if you want it at the top.
    3. You could also make minor adjustments by adding spaces in the header/footer string.

Posting Permissions

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