Printing schedule with auto increment workday date

J

Jenn P.

Hi,

I'm trying to set up what I think should be a macro that runs on a print
event.

I have an office schedule that I print out for others to fill in. I'd like
the date in merged cells c1:j1 to have the date (dddd, mmmm d, yyyy) be in
that cell and automatically increase by 1 day for every page printed out,
excluding Saturday and Sunday. Is this possible?

I've been trying to figure out a way and so far have failed. Does anyone
have any hints, suggestions, or tips that'll get me going in the right
direction?

Much appreciated!
 
D

Dave Peterson

The bad news is that anything that relies on intercepting the
workbook_beforeprint will react to a file|print preview, too.

I think I'd give the user a dedicated macro that would add one (or 3) to the
date and then print that:

Option Explicit
Sub testme()
With Worksheets("sheet1")
With .Range("C1")
If IsNumeric(.Value2) Then
If Weekday(.Value) = vbFriday Then
.Value = .Value + 3
Else
.Value = .Value + 1
End If
.Parent.PrintOut preview:=True
End If
End With
End With
End Sub


I used preview:=true for testing purposes.
 
D

Dave Peterson

January 1, 2007 to February 28, 2007?

Option Explicit
Sub testme()
Dim dCtr As Long

With Worksheets("sheet1")
For dCtr = DateSerial(2007, 1, 1) To DateSerial(2007, 2, 28)
If Weekday(dCtr) = vbSaturday _
Or Weekday(dCtr) = vbSunday Then
'do nothing
Else
.Range("C1").Value = dCtr
'you may not need this next line
.Range("C1").EntireColumn.AutoFit
.PrintOut preview:=True
End If
Next dCtr
End With

End Sub
 
Top