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).W eight = xlThick
Next: Sheets(1).Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

--
David