Copying partial row in Excel to new sheet automatically when 2 specific conditions are met

I am working in Excel 2010. I would like to automate the following process: if the following 2 conditions are met within the same row, then it will automatically trigger a copy and "paste values" of part of that row into the first empty row of another sheet:

If, in worksheet called "Worksheet 1":
A cell in any row in Column V: is not blank
A cell in the same row as above in Column X: = "Yes"
Then the following cells from that row are copied and pasted, as values, into same row to "Worksheet 2", mapped as follows:
Column V value to Column A
Column W value to Column B
Column A value to Column C
Column B value to Column D
Column L value to Column E
Column M value to Column F

Further details:
If there are blank values in any of the cells being copied from Worksheet 1, then the cell in Worksheet 2 would also show as blank.
The values are to be copied over just once into Worksheet 2.
The process would happen automatically anytime the above two conditions are met.

Thanks,
Andrea
AndreamaryAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Peter KwanAnalyst ProgrammerCommented:
Please try the attached.
Temp.xlsm
0
Rob HensonFinance AnalystCommented:
Have you looked at the Advanced Filter function?

This allows you to specify selection criteria for a data table and it will filter the table either in place or copy the matching rows to another location. This can copy only specific columns if so required, based on the headers of the columns.

If you need more information for this let us know.

Thanks
Rob H
0
AndreamaryAuthor Commented:
Thanks for the temp.xlsm file...it is almost working exactly as I want it to, except that the values are not being copied into the next available empty row on Worksheet 2, resulting in unwanted blank rows. So if there are 10 rows in Worksheet 1, and rows 2, 3, 5, 7, 9, and 10 meet the criteria to be copied over, these 6 rows would be copied over to rows 1 through 6 on Worksheet 2. If the code can be fixed to do that, that would be great.

Thanks, Rob H, for the advanced filter suggestion. I'd like to go the VBA route for this particular situation.

Thanks,
Andrea
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Peter KwanAnalyst ProgrammerCommented:
Andrea, it would be easy to modify the program, but would you please clarify the following first?

If there are 10 rows in Worksheet 1 and row 2,3,5,7,9,10 meets the criteria.
1) If row 4 updates the data to meet the criteria, then where in Worksheet 2 should the row be copied to? The end or insert between 3 and 5?
2) Will there be a case that column A, B, L, M, V, W are all empty for a row in Worksheet 1 that meets the criteria?
0
Rob HensonFinance AnalystCommented:
The AF can be initiated through vba if so required.

Thanks
Rob
0
AndreamaryAuthor Commented:
Thanks for letting me know that, Rob. My workbook is shared, which appears to make the AF option unavailable.

Pkwan, in answer to your questions:
#1 - If row 4 meets the criteria, then it would insert at the end. The plan is that Worksheet 2 will have column filters allowing me to sort the data (assuming that feature won't interfere with what you are proposing?)

#2 - No

Thanks,
Andrea
0
Rob HensonFinance AnalystCommented:
Shared!!!

The general consensus of opinion on Experts Exchange regarding Shared Workbooks is to avoid them wherever possible!! They will end up corrupt in some way or other.

Thanks
Rob H
0
Peter KwanAnalyst ProgrammerCommented:
Please try the attached. Thanks.
Temp.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AndreamaryAuthor Commented:
Yes, Rob, I agree...unfortunately the decision to use shared Excel files is beyond my control so I'm making daily backups of the file while also taking every opportunity to encourage the organization to switch to Access!!

Andrea
0
AndreamaryAuthor Commented:
Works perfectly. Thanks so much!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.