Copy/Paste rows based on certain criteria (drop down or colors)

[jholiday78]

Good Evening,

I need help with a VBA code to program my excel spreadsheet to copy and paste a row from my master spreadsheet based on certain criteria. The criteria could be either: 1) the rows that are colored green (because the are conditionally formatted to turn green because if the drop down text "Yes" is selected)
in or 2) the drop down word "yes" is selected in column "W".

Basically what I am trying to do is copy and paste from (Sheet 1) to a blank sheet (Sheet 2) that will automatically refresh with the new information when either of these criteria's change.

Other question because I don't know much about vba yet:
1) Does it matter if I already have a button that is filtering Sheet 1 by dates? I have tried multiple codes from my google search's, and nothing seems to work.

Thanks in advance for your help!
Jessica

[humdingaling]

conditional format doesn't hold the color data
so really you just want to base on your criteria for conditional format which is column W = Yes

to mimic conditional format you would probably need event triggered macro to look at column W

[humdingaling]

Please Login or Register  to view this content.

[jholiday78]

Questions:

1)I input copy and pasted the macro below into visual basic. I noticed that it doesn't name Sheet 1. How will it now where to pull the information from? I have other sheets in my workbook that I couldn't include in my attachment for review.

2)I am getting a pop up box after I press the play button. The pop up box is titled Macros and it lists all the macros for other worksheets in my workbook. What should I do?

[jholiday78]

Everything works perfect in the test document. But when I try to apply the macro to my original document, it doesn't work. I keep getting the macro pop-up box when I run the macro. I am not sure what I am doing wrong. Help!

[humdingaling]

Questions:

1)I input copy and pasted the macro below into visual basic. I noticed that it doesn't name Sheet 1. How will it now where to pull the information from? I have other sheets in my workbook that I couldn't include in my attachment for review.

2)I am getting a pop up box after I press the play button. The pop up box is titled Macros and it lists all the macros for other worksheets in my workbook. What should I do?

to answer Q1
it knows it is the current sheet as the code is event driven based on the sheet the code lies
if you are concerned you can add your sheet name into references


Q2.
not sure where you put the code....
the code is event driven and not "RUN" with play
brief reading on event coding
http://www.excel-easy.com/vba/events.html

[jholiday78]

That link helped a lot to enter the event in the correct place. Thank you... I apologize I am really knew to this. Any other links that you want to send along my way for future reference would be greatly appreciated. I learn something new every day in excel.

I was able to find an error from that point. When I would enter the code that you gave me it would delete my Sheet 1 with all my data. The only thing I changed in the code was the 2 in Sheet 2 because it was clearing the data in Sheet 1. No error on your part. It is the way the sheets are named in my document on Visual Basic.

How it looked in my Visual Basic left drop down list:
Sheet 12(Sheet 2)
Sheet 2(Sheet 1)

What code you gave me:
Sheet2.Cells.Clear
'Clears Sheet 2

What I changed the code to:
Sheet12.Cells.Clear

Thank you again for all your help. It came just in time. My meeting starts in 30 minutes and now I don't have to say, "No, I couldn't figure out how to do what you asked me to do"!

[humdingaling]

that's great to hear

i prefer to pass on knowledge rather than just straight solutions

If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Cheers
Hum