Printing schedule with auto increment workday date

[Guest]

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!

 

[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.

 

[Guest]

Thank you ever so much, Dave! I appreciate your help. And, I don't mind the
print preview. (It's helpful.)

I know I will be asked this next question, and that is, is it possible to
print a range, say from January 2007 to February 2007, at once, instead of
day by day?

Again, much appreciated.

 

[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

 

[Guest]

Thank you Dave! You've been a tremendous help. Thank you so much!

Cheers!
Jenn

 

[Dave Peterson]

Glad it worked for you.

Thank you Dave! You've been a tremendous help. Thank you so much!

Cheers!
Jenn