How do i set a date code in macro to tell it to select dates in column A from
only the last 5 working days, including the current day, in sheet called "1".

thanks.

With Sheets(1)
RowCount = 1
Do While .Range("A" & RowCount) <> ""
StartDate = .Range("A" & RowCount)
EndDate = Date
wrkdays = DateDiff("d", StartDate, EndDate) - _
(DateDiff("w", StartDate, EndDate, vbSaturday) + _
DateDiff("w", StartDate, EndDate, vbSunday)) + 1
If wrkdays <= 5 Then

End If
RowCount = RowCount + 1
Loop
End With

"Zak" wrote:

> How do i set a date code in macro to tell it to select dates in column A from
> only the last 5 working days, including the current day, in sheet called "1".
>
> thanks.

Hi,

I tried it but it just highlights the lines and doesnt do anything:

wrkdays = DateDiff("d", StartDate, EndDate) - _
(DateDiff("w", StartDate, EndDate, vbSaturday) + _
DateDiff("w", StartDate, EndDate, vbSunday)) + 1

You mention sat and sun is this to exclude them (just trying to understand
code)?

Also, are you able to put the code as a whole macro? i get confused trying
to do a beginning and an end.

sorry! appreciate your help.

"Joel" wrote:

> With Sheets(1)
> RowCount = 1
> Do While .Range("A" & RowCount) <> ""
> StartDate = .Range("A" & RowCount)
> EndDate = Date
> wrkdays = DateDiff("d", StartDate, EndDate) - _
> (DateDiff("w", StartDate, EndDate, vbSaturday) + _
> DateDiff("w", StartDate, EndDate, vbSunday)) + 1
> If wrkdays <= 5 Then
>
> End If
> RowCount = RowCount + 1
> Loop
> End With
>
> "Zak" wrote:
>
> > How do i set a date code in macro to tell it to select dates in column A from
> > only the last 5 working days, including the current day, in sheet called "1".
> >
> > thanks.

Sorry forgot to mention, once it has filtered for the dates within the last 5
days it should copy only columns A,C,F,G,U,Z to sheet "2" within the same
workbook.


thanks.

"Zak" wrote:

> Hi,
>
> I tried it but it just highlights the lines and doesnt do anything:
>
> wrkdays = DateDiff("d", StartDate, EndDate) - _
> (DateDiff("w", StartDate, EndDate, vbSaturday) + _
> DateDiff("w", StartDate, EndDate, vbSunday)) + 1
>
> You mention sat and sun is this to exclude them (just trying to understand
> code)?
>
> Also, are you able to put the code as a whole macro? i get confused trying
> to do a beginning and an end.
>
> sorry! appreciate your help.
>
> "Joel" wrote:
>
> > With Sheets(1)
> > RowCount = 1
> > Do While .Range("A" & RowCount) <> ""
> > StartDate = .Range("A" & RowCount)
> > EndDate = Date
> > wrkdays = DateDiff("d", StartDate, EndDate) - _
> > (DateDiff("w", StartDate, EndDate, vbSaturday) + _
> > DateDiff("w", StartDate, EndDate, vbSunday)) + 1
> > If wrkdays <= 5 Then
> >
> > End If
> > RowCount = RowCount + 1
> > Loop
> > End With
> >
> > "Zak" wrote:
> >
> > > How do i set a date code in macro to tell it to select dates in column A from
> > > only the last 5 working days, including the current day, in sheet called "1".
> > >
> > > thanks.