Auto Change Date When Printing

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.

Try This:

Hercules1946 said:

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

Click to expand...

Thanks mate, sorry for being completely useless, but what do I do with the code? Do I open some sort of complex editor?

Hercules1946 said:

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

Click to expand...

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.

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

sal1tech said:

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.

Click to expand...

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.