Not very printer friendly

[libby]

Hi

THE SITUATION
I am required to produce a spreadsheet that prints out the
same page for every day between two dates - sometimes as
many as a whole years worth.
Each page is to have the day and date at the top of it
eg the first page will have "Wednesday, 01-Jan-2004", the
second page "Thursday, 02-Jan-2004" etc

I initially tried putting the day and date into the
header, but couldn't format the date variable to font size
14 (any ideas - let me know!).

I got round this by using 3 merged cells in the top row.

THE PROBLEM
To avoid having a workbook with 365 pages, I've coded it
to put the day and date in range("a1"), print it,
increment the day and date, print it again - and so on,
for each of the dates specified by the user.
However, printing this many pages so quickly exceeds the
printer memory, sometimes causing it to suffer from
amnesia. To try to overcome this, I've put a 3 month limit
on the number of days that can be printed at any one time.

Is there an easier, more printer friendly way of doing
this???

My code is this:

Private Sub cmdPrint_Click()
Dim DATEmax
Dim MYDATEstart
Dim MYDATEend
'PRODUCE ERROR MESSAGE IS INVALID DATES ARE ENTERED
If IsDate(txtStart.Text) = False Then
MsgBox "Start Date is invalid.", VbCritical, "Microsoft
Excel - Invalid Date"
txtStart.SetFocus
txtStart.SelStart = 0
txtStart.SelLength = 8
ElseIf IsDate(txtEnd.Text) = False Then
MsgBox "End Date is invalid.", vbCritical, "Microsoft
Excel - Invalid Date"
txtEnd.SetFocus
txtEnd.SelStart = 0
txtEnd.SelLength = 8
Else
MYDATEstart = DateValue(txtStart.Text)
MYDATEend = DateValue(txtEnd.Text)
DATEmax = DateAdd("m", 3, MYDATEstart) - 1

'PRODUCE ERROR MESSAGE IF THE START DATE IS NOT LESS THAN
'END DATE

If MYDATEstart > MYDATEend Then
MsgBox "Start Date must be less than or equal to End
Date.", vbCritical
txtStart.SetFocus
txtStart.SelStart = 0
txtStart.SelLength = 8

'LIMIT THE END DATE TO NO MORE THAN 3 MONTHS FROM
START 'DATE
ElseIf MYDATEend > DATEmax Then
MsgBox "A max of 3 months is allowed under any one
print." & vbCrLf & " " & vbCrLf & _
"Three months from your Start Date is: " & Format
(DATEmax, "dd/mm/yy") & vbCrLf & " " & vbCrLf & _
"This date will automatically populate your End
Date field.", vbInformation, "Max ~ Three Months"
txtEnd.Text = Format(DATEmax, "dd/mm/yy")
txtEnd.SetFocus
txtEnd.SelStart = 0
txtEnd.SelLength = 8
Else
If MsgBox("Click OK to print.", vbInformation
+ vbOKCancel) = vbOK Then

Select Case CheckboxReverse
Case False 'Reverse printing is off
Sheet2.Range("a1") = Format(MYDATEstart, "dddd, dd-mmm-
yyyy")
Sheet2.PrintOut

Do While MYDATEstart < MYDATEend
MYDATEstart = MYDATEstart + 1
Sheet2.Range("a1") = Format(MYDATEstart, "dddd, dd-mmm-
yyyy")
Sheet2.PrintOut
Loop

Case True 'Reverse printing is on
Sheet2.Range("a1") = Format(MYDATEend, "dddd, dd-mmm-
yyyy")
Sheet2.PrintOut

Do While MYDATEend > MYDATEstart
MYDATEend = MYDATEend - 1
Sheet2.Range("a1") = Format(MYDATEend, "dddd, dd-mmm-
yyyy")
Sheet2.PrintOut
Loop
End Select
Sheet2.Range("a1") = ""
Unload Me
End If
End If
End If
End Sub