Excel macro that will print a page for every business day 1

[scudsimmons]

I need a macro that will print a cover sheet with the heading "Daily Work" and the date below it. I would like to run this macro and have it print every business day so i do not have to change the date and print 260 times.

 

[xlbo]

might wanna have a look in the help files 1st then...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xplo5iv]

Hi

If you want to print mon - fri the following will work, and print a year's worth. Handling holidays etc requires more work...

Sub Weekdays()
'
Dim dteDay As Date

dteDay = Date
Do Until dteDay = Date + 365
dteDay = dteDay + 1
If Weekday(dteDay) > 1 And Weekday(dteDay) < 7 Then
Worksheets("Sheet1").Range("A1").Value = dteDay
Worksheets("Sheet1").PrintOut Copies:=1, Collate:=True

End If

Loop

End Sub

 

[scudsimmons]

Is there a way to have it loop for 30 days worth and prompt for a continue? Reason being just in case they want to end the loop/printing.

 

[SkipVought]

Hi,

Check out the InputBox.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[SkipVought]

Hi,

Check out the InputBox Function.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[xplo5iv]

You could do it like this...

Sub Weekdays()
'
Dim dteDay As Date
Dim dteStart As Date
Dim intPrint As Integer
dteDay = Date
dteStart = Date

printsheets:

Do Until dteDay = dteStart + 30
dteDay = dteDay + 1
If Weekday(dteDay) > 1 And Weekday(dteDay) < 7 Then
Worksheets("Sheet1").Range("A1").Value = dteDay
Worksheets("Sheet1").PrintOut Copies:=1, Collate:=True
End If
Loop

dteStart = dteStart + 30
intPrint = MsgBox("Print next 30?", 4)
If intPrint = 6 Then GoTo printsheets

End Sub

And maybe use the inputbox function to give the start date, rather than using the current date?

dteStart = Application.InputBox("Enter Start Date")

HTH