copy data from start to end range

**stoey** · 05-02-2009, 11:59 AM

hi,

can anyone help me how to create a macro which will copy range of data based on a criteria? I have a worksheet which contains dates on column C rows C30:C64, dates would start from march 29 to may 2. how can I copy the range of data from April 1 to April 30? the criteria which will be copied will always be the start of the of the month up to the end of the month...meaning if the dates placed is like April 26 to June 6, it would automatically extract the range for May 1 to May 31 and place this on another sheet.

regards,

stoey

**martindwilson** · 05-02-2009, 01:22 PM

here's a non vba approach see sheet 2

**stoey** · 05-02-2009, 06:18 PM

hi martin,

i appreciate your response...i checked the attachement you sent and under sheet2, column A rows 2-31 returns #NAME? error any ideas?

regards,

stoey

**martindwilson** · 05-02-2009, 06:23 PM

it uses EOMONTH function, you need to activate the analysis toolpak
tools/addins
check the analysis toolpak box.
(note the actual formulas go down to row 31 to allow for all months and are just not showing anything as in this example april only has 30 days)

**stoey** · 05-02-2009, 06:28 PM

wow! that works...im not familiar with the uses of add ins...this is another learning...thanks martin... is there any possibility of using a formula without having to use the add ins?

thanks,

stoey

**martindwilson** · 05-02-2009, 07:16 PM

that addin is a standard part of excel dont know why its not activated/included as default
BUT you could replace from a2 dragged down with
in sheet2 a2

=IF(ISERROR(A1+1>DATE(YEAR(A1),1+MONTH(A1),1)-1),"",IF(A1+1>DATE(YEAR(A1),1+MONTH(A1),1)-1,"",A1+1))
that should leave blanks if month only has 28/29/30 days and doesnt require eomonth function
also i had a rethink
since you will only have one total month and a bit each side , we can use the median(or average) of those dates to determine which month we require to extract, that cuts the formula in sheet2!a1 to something slimmer

**stoey** · 05-03-2009, 01:24 AM

hi there martin,

thank you so much for the help...the sample workbook you gave me is just perfect for what I need...thanks a lot! you rock!

regards,

stoey