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

--
cooter2
-----------------------------------------------------------------------
cooter24's Profile: http://www.excelforum.com/member.php...fo&userid=2961
View this thread: http://www.excelforum.com/showthread.php?threadid=49952

On Mon, 9 Jan 2006 14:37:31 -0600, cooter24
wrote:


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.

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?


--
cooter24
------------------------------------------------------------------------
cooter24's Profile: http://www.excelforum.com/member.php...o&userid=29619
View this thread: http://www.excelforum.com/showthread...hreadid=499525

On Mon, 9 Jan 2006 16:19:33 -0600, cooter24
wrote:


Ron,

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

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?



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

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

Ron Rosenfeld wrote

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


Misunderstood intent of excluding holidays. I thought that meant not
accounting for them at all. Sorry if I wasted everyone's time.

--
David

David wrote

If Month(.Value) < Month(StartDt) Then .ClearContents

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

On Wed, 11 Jan 2006 13:52:49 -0800, David wrote:

David wrote

If Month(.Value) < Month(StartDt) Then .ClearContents

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 :)

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