Avatar of Andreamary
Andreamary asked on

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
Microsoft Excel

Avatar of undefined
Last Comment
Andreamary

8/22/2022 - Mon
Peter Kwan

Please try the attached.
Temp.xlsm
Rob Henson

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
ASKER
Andreamary

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
Peter Kwan

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?
Rob Henson

The AF can be initiated through vba if so required.

Thanks
Rob
ASKER
Andreamary

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rob Henson

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
ASKER CERTIFIED SOLUTION
Peter Kwan

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Andreamary

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
ASKER
Andreamary

Works perfectly. Thanks so much!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck