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" wrote in message
...

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