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.
On Mon, 9 Jan 2006 14:37:31 -0600, cooter24
<[email protected]> 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?
On Mon, 9 Jan 2006 16:19:33 -0600, cooter24
<[email protected]> 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).Weight = xlThick
Next: Sheets(1).Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
--
David
On Tue, 10 Jan 2006 02:41:40 -0800, David <[email protected]> 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 <[email protected]>
> 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 <[email protected]> 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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks