Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying rows based on date
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying rows based on date
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying rows based on date
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" 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying rows based on date
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying rows based on date
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying a row based on date | Excel Worksheet Functions | |||
Copying rows from 2 sheets to a new worksheet based on date criter | Excel Discussion (Misc queries) | |||
Copying rows to different worksheets based on a cells value | Excel Programming | |||
Copying whole rows based upon one criteria | Excel Discussion (Misc queries) | |||
Copying rows of data based on a value in a cell. | Excel Programming |