On Tue, 10 Jan 2006 02:41:40 -0800, David wrote:

cooter24 wrote


Ron,

Thanks for your response. How would I add the network days function
into the autofill weekdays code?



I've been watching this thread and thought I'd offer my 2-cents.
I have a few workbooks that I want new weekday dates each month.
Weekdays in these workbooks are in the range C2:AA2, separated in 5-day
blocks by thick borders. The routine I'm posting does the work for each
of 5 sheets in one particular workbook. It gets its starting date early
on and puts it in C2, then autofills, then clears any dates not in the
new month, then adds back borders. Of course you could adapt it to a
single sheet and change ranges to suit your needs.

Sub NewMonth()
Dim sh As Worksheet, c As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each sh In Sheets(Array(1, 2, 3, 4, 5))
If sh.Range("W2") 1 And sh.Range("AA2") 1 Then
sh.Range("C2") = sh.Range("W2") + 7
ElseIf sh.Range("W2") = 0 And sh.Range("AA2") = 0 Then
sh.Range("C2") = sh.Range("R2") + 7
Else: sh.Range("C2") = sh.Range("W2")
End If
sh.Range("C2").AutoFill Destination:=sh.Range("C2:AA2"), _
Type:=xlFillWeekdays
For Each c In sh.Range("C2:G2")
If Day(c.Value) 24 Then c.ClearContents
Next
For Each c In sh.Range("W2:AA2")
If Day(c.Value) < 8 Then c.ClearContents
Next
sh.Range("G2,L2,Q2,V2,AA2").Borders(xlEdgeRight). Weight = xlThick
Next: Sheets(1).Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Without testing it, it looks appropriate.

Since the OP was asking about excluding holidays, I would probably suggest
changing the auto-fill to a routine using the Analysis Tool Pak function
"Workday". I would set a reference to atpvbaen.xls

The Holiday list could either be an array within the macro, or it could be on a
range in the worksheet.

Something like:

========================
..
..
..
Holidays = Array(DateSerial(2006, 1, 1), _
DateSerial(2006, 5, 30), DateSerial(2006, 7, 4))
StartDt = DateSerial(2006, 7, 1)

For i = 2 To 25
With DtLabels(1, i)
.Value = workday(StartDt - 1, i, Holidays)
.NumberFormat = "ddd dd-mmm-yyy"
If Month(.Value) < Month(StartDt) Then .ClearContents
End With
Next i
..
..
..
=================================


--ron