Macro to print series of dates

[Keith]

Good afternoon

I have a worksheet which when printed needs a date in cell G1. That's the
easy part...

What I am trying to achieve is that the user inputs a start date and an end
date and for each sheet printed the first has the start date in G1, the
second has G1+1 through to the end date excluding Saturdays and Sundays and
ideally other dates on an exception list e.g. Bank Holidays.

Hopefully that is clear but I am at a complete loss as to where to even
start so would really appreciate some help.

Many thanks

Kewa

 

[Bernie Deitrick]

Kewa,

List your holidays somewhere on your sheet, and name the range "Holidays" (without the quotes).

Then insert a userform into your project, and put two datetimepickers (named DTPicker1 and
DTPicker2 - You will need a reference to MS Windows Common Controls to use the datetimepickers) onto
the form, with a commandbutton. Use labels to show that the first DTPicker is the Start Date and the
second is the End Data, then use this code for the commandbutton (replace "SheetName" with the
actual sheet name):

Private Sub CommandButton1_Click()
Dim myDate As Date
Dim myStartDate As Date
Dim myEndDate As Date
myStartDate = Me.DTPicker1.Value
myEndDate = Me.DTPicker2.Value
For myDate = myStartDate To myEndDate
If Format(myDate, "dddd") <> "Saturday" And _
Format(myDate, "dddd") <> "Sunday" And _
IsError(Application.Match(CLng(myDate), Range("Holidays"), False)) Then
Worksheets("SheetName").Range("G1").Value = myDate
Worksheets("SheetName").PrintOut
End If
Next myDate
End Sub


Then use this macro to show the userform:

Sub PickDatesToPrint()
Load UserForm1
UserForm1.Show
End Sub

HTH,
Bernie
MS Excel MVP