Priting multiple pages with ascending dates?

[e_rock407]

Very simple question for someone who knows excel inside and out... I made a VERY simple time sheet for daily usage at a job site. I want to print like 100 pages with the date on it, example: (page 1) Date: 11/7/2011, (page 2) Date: 11/8/2011, etc. but I dont want it in a header.

Is there simply a formula to put in the desired cell for the date that will plug in a date(starting today) and count up 1 day on every page

***If someone can answer this rather quickly it would be a TREMENDOUS help***

 

[Comfy]

I'm not sure about a formula but could do a quick macro to do taht automatically for you?

Would the 1st page always be todays date?
and which cell would this be in?

/Comfy

 

[e_rock407]

yes always start at todays date and go out, say, 90 days (so i can go back and print another 90 when I've used all the sheets)

cells are J4&5 and K4&5 (all merged together)

again, it is a very simple time sheet for my field guys. I just dont want to have to pint 90 individual pages and manually put in the date on each page before printing.

I dont think Ive used macros, so if you could give me a step by step it would help a great deal

-Erik

 

[e_rock407]

I can use just 1 cell too, if it makes the macro easier, was just trying to make the space larger for a bigger font

 

[Comfy]

In your workbook press Alt + F11.
This will open the Visual Basic Editor.
Right click on the Workbook you are working on.

E.G.
If your workbook is called "TimeSheet" you should right click
VBAProject (TimeSheet)

Then Select insert module.

Paste this code into the module.

Sub PageDates()
Dim i As Long
Dim xlRow As Long, xlCol As Long
Dim xlSheet As Worksheet

Set xlSheet = ActiveSheet
xlRow = 4
xlCol = 10
    If xlSheet.VPageBreaks.Item(1).Location.Column < xlCol Then MsgBox "Column " & xlCol & "does not fit onto the page, please amend and retry.": Exit Sub
    Cells(xlRow, xlCol).Value = Date
    For i = 2 To xlSheet.HPageBreaks.Count
        Cells(xlSheet.HPageBreaks.Item(i - 1).Location.Row + xlRow - 1, xlCol).Value = Date + (i - 1)
    Next i
End Sub

This will add what you have requested to cell that intersect Row 4 and Column 10 of each sheet.

You can change this if you wish by amending:

xlRow = 4
xlCol = 10

In the code.

Edit:
After posting I have had a thought, please don't run this yet. I need to test with more than 10 pages and will post the appropriate changes

 

[Comfy]

Ok a couple more questions to certify that this will work in your circumstances.

Do you pages run down Excel?
(Does the top and bottom of each page touch)

or do they run from left to right?
(Does each side of the page touch each other)

 

[e_rock407]

neither, I made 1 page and want to print like 90 copies, but i want each print to have its own date starting from today and going out, pretty much how every many copies i print

 

[Comfy]

Ahh I see.

In that case ignore everything I have posted. will have a think but not sure I can help.

 

[e_rock407]

thats fine, if you have any other suggestions of how i could do it, Im all ears. youve told me more than i could find searching the web for like a day and a half, thank you.

 

[Comfy]

The only way I can think that this would be possible would be to run a macro that will add the date, print, add the next date, print, etc etc.

So it might not be the fastest way of doing this.

If you could post your time sheet I could probably help you convert it into a weekly or bi-weekly so you would only have to print out once a week?