AutoFill Weekdays problem

  • Thread starter Thread starter cooter24
  • Start date Start date
C

cooter24

Hello,

I am fairly new to programming so bear with me. I have a number o
sheets that I have to update monthly which I am trying to automate.
have been able to do everything but avoid pasting holidays when I us
autofill and paste special weekdays. Is there anway, similar to th
networkdays function, in programming where I can only pastespecia
actual workdays and avoid the holidays? I know I can use a look u
functon and then delete but I would rather avoid that. Any help i
appreciated
 
Hello,

I am fairly new to programming so bear with me. I have a number of
sheets that I have to update monthly which I am trying to automate. I
have been able to do everything but avoid pasting holidays when I use
autofill and paste special weekdays. Is there anway, similar to the
networkdays function, in programming where I can only pastespecial
actual workdays and avoid the holidays? I know I can use a look up
functon and then delete but I would rather avoid that. Any help is
appreciated.
Click to expand...

Why not use the networkdays function, with the optional holidays argument,
within VBA to generate your list of weekdays?

You can set a reference to atpvbaen.xls in VBA and then use the ATP functions
directly.

No matter what, you'll still need a list of the holiday dates someplace.
--ron
 
Ron,

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

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

Perhaps I misunderstood; I thought you were using code now to generate the
weekdays. If you are, please post the code.

If not, please be more descriptive about exactly what you are doing.


--ron
 
cooter24 wrote
Ron,

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

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
 
cooter24 wrote


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
Click to expand...

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
 
Ron Rosenfeld wrote
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
Click to expand...

Misunderstood intent of excluding holidays. I thought that meant not
accounting for them at all. Sorry if I wasted everyone's time.
 
David wrote
If Month(.Value) <> Month(StartDt) Then .ClearContents
Click to expand...

I *was* able to adapt this to simplify my original routine for purging
dates that weren't in the new month:

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

Was shortened to:

For Each c In sh.Range("C2:AA2")
If Month(c.Value) <> Month(Range("G2")) Then c.ClearContents
Next

So lurking here wasn't a total loss :)
 
David wrote


I *was* able to adapt this to simplify my original routine for purging
dates that weren't in the new month:

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

Was shortened to:

For Each c In sh.Range("C2:AA2")
If Month(c.Value) <> Month(Range("G2")) Then c.ClearContents
Next

So lurking here wasn't a total loss :)
Click to expand...

I know thatt *I* have learned a lot by lurking here and trying to help others.
I think I've probably learned more new things, that way.


--ron
 
Back
Top