PDA

View Full Version : Copy rows to 2 sheets using criteria



moroformat
01-21-2013, 09:19 PM
I copy data to the sheet "3" in a daily basis so the row count vary every day depending on the date range I need. I need only the data in the rows with green headings from sheet "3" to go the corresponding headings in sheets "1" and "2" with the following criteria:

I need all of the rows with green headings from sheet "3" with the word criteria "Asset" from column "BJ" copyied to the green section of sheet "1" after clicking a button called run in sheet "1".
I also need all of the rows with green headings from sheet "3" with the word "criteria "Stock" from column "BJ"copyied to the green section of sheet "2" after clicking a button called run in sheet "2".

I need the macro to also keep the two rows of space between the green and yellow sections in sheets "1" and "2" regardless of how many data it is copyied to the green section. In other words I would like it if the green section autosizes so that there is always a 2-row space between the green and yellow section after the data is pasted in sheets "1" and "2".

Thank you for your time and I would really appreciate some assistance with this Macro.

Attached is the excel file.

Thanks,

moroformat

werafa
01-22-2013, 09:53 PM
Hi Moroformat,

I am not too clear on your exact needs, but have some ideas to get you moving in the right direction.

First, read up on how to work with ranges.
Then declare the area you might wish to copy as a range
dim myRange as range
set myrange = activesheet.range("A:D")
'or
set myrange = activesheet.usedrange
once you have done this, you can use the locals window from the VBA editor to view the various properties of cells - and then write code to test the property.

if myrange(1,1).Interior.Color = vbgreen then
sheet2.range("A:A") = myrange.columns(1)
end if
you can also test cell values by using the .value property

Regards
Tim