Copying rows based on date

[flurry]

I'm trying to create a macro to look for a start date and end date in one column and copy the rows that fall in between onto another sheet. I then need to set another macro that emails that as an attachment. Any ideas would be much appreciated! p.s. I'm very new to VBA!

[Ron de Bruin]

hi flurry

You can install EasyFilter to filter and copy to a new sheet
http://www.rondebruin.nl/easyfilter.htm

Or code here
http://www.rondebruin.nl/copy5.htm#one

use code like this in this macro
rng.AutoFilter Field:=1, Criteria1:=">=" & DateSerial(1947, 2, 23), _
Operator:=xlOr, Criteria2:="<=" & DateSerial(1988, 5, 7) ' yyyy-mm-dd format


Then you can send that sheet with my SendMail add-in or with code from this page
http://www.rondebruin.nl/sendmail.htm


--
Regards Ron De Bruin
http://www.rondebruin.nl



"flurry" <[email protected]> wrote in message
news:[email protected]...
>
> I'm trying to create a macro to look for a start date and end date in
> one column and copy the rows that fall in between onto another sheet. I
> then need to set another macro that emails that as an attachment. Any
> ideas would be much appreciated! p.s. I'm very new to VBA!
>
>
> --
> flurry
> ------------------------------------------------------------------------
> flurry's Profile: http://www.excelforum.com/member.php...o&userid=34303
> View this thread: http://www.excelforum.com/showthread...hreadid=548113
>

[flurry]

Hi Ron
thanks for this - only thing I'm not sure of is where to put the

rng.AutoFilter Field:=1, Criteria1:=">=" & DateSerial(1947, 2, 23), _
Operator:=xlOr, Criteria2:="<=" & DateSerial(1988, 5, 7) ' yyyy-mm-dd format

bit within the code.

Many thanks

[Ron de Bruin]

Try this one

Sub Copy_With_AutoFilter1_test()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range

Set WS = Sheets("sheet1") '<<< Change
'A1 is the top left cell of your filter range and the header of the first column
Set rng = WS.Range("A1").CurrentRegion '<<< Change

'Close AutoFilter first
WS.AutoFilterMode = False

'This example filter on the first column in the range (change the field if needed)
rng.AutoFilter Field:=1, Criteria1:=">=" & DateSerial(1947, 2, 23), _
Operator:=xlOr, Criteria2:="<=" & DateSerial(1988, 5, 7) ' yyyy-mm-dd format

Set WSNew = Worksheets.Add
WS.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With
WS.AutoFilterMode = False

End Sub



--
Regards Ron De Bruin
http://www.rondebruin.nl



"flurry" <[email protected]> wrote in message
news:[email protected]...
>
> Hi Ron
> thanks for this - only thing I'm not sure of is where to put the
>
> rng.AutoFilter Field:=1, Criteria1:=">=" & DateSerial(1947, 2, 23), _
> Operator:=xlOr, Criteria2:="<=" & DateSerial(1988, 5, 7) ' yyyy-mm-dd
> format
>
> bit within the code.
>
> Many thanks
>
>
> --
> flurry
> ------------------------------------------------------------------------
> flurry's Profile: http://www.excelforum.com/member.php...o&userid=34303
> View this thread: http://www.excelforum.com/showthread...hreadid=548113
>

[Dave Peterson]

And just to add to Ron's response...

Sometimes filtering dates using code doesn't work as expected.

To the OP:
If you find that the dates aren't being found, you could try this minor change:

rng.AutoFilter Field:=1, Criteria1:=">=" & Clng(DateSerial(1947, 2, 23)), _
Operator:=xlOr, Criteria2:="<=" & clng(DateSerial(1988, 5, 7))

Sometimes it helps (sometimes, it doesn't).

Ron de Bruin wrote:
>
> Try this one
>
> Sub Copy_With_AutoFilter1_test()
> Dim WS As Worksheet
> Dim WSNew As Worksheet
> Dim rng As Range
>
> Set WS = Sheets("sheet1") '<<< Change
> 'A1 is the top left cell of your filter range and the header of the first column
> Set rng = WS.Range("A1").CurrentRegion '<<< Change
>
> 'Close AutoFilter first
> WS.AutoFilterMode = False
>
> 'This example filter on the first column in the range (change the field if needed)
> rng.AutoFilter Field:=1, Criteria1:=">=" & DateSerial(1947, 2, 23), _
> Operator:=xlOr, Criteria2:="<=" & DateSerial(1988, 5, 7) ' yyyy-mm-dd format
>
> Set WSNew = Worksheets.Add
> WS.AutoFilter.Range.Copy
> With WSNew.Range("A1")
> ' Paste:=8 will copy the columnwidth in Excel 2000 and higher
> .PasteSpecial Paste:=8
> .PasteSpecial xlPasteValues
> .PasteSpecial xlPasteFormats
> Application.CutCopyMode = False
> .Select
> End With
> WS.AutoFilterMode = False
>
> End Sub
>
> --
> Regards Ron De Bruin
> http://www.rondebruin.nl
>
> "flurry" <[email protected]> wrote in message
> news:[email protected]...
> >
> > Hi Ron
> > thanks for this - only thing I'm not sure of is where to put the
> >
> > rng.AutoFilter Field:=1, Criteria1:=">=" & DateSerial(1947, 2, 23), _
> > Operator:=xlOr, Criteria2:="<=" & DateSerial(1988, 5, 7) ' yyyy-mm-dd
> > format
> >
> > bit within the code.
> >
> > Many thanks
> >
> >
> > --
> > flurry
> > ------------------------------------------------------------------------
> > flurry's Profile: http://www.excelforum.com/member.php...o&userid=34303
> > View this thread: http://www.excelforum.com/showthread...hreadid=548113
> >

--

Dave Peterson