asked on 9/4/2013

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

Last Comment

Andreamary

9/6/2013

Peter Kwan

9/4/2013

Please try the attached.
Temp.xlsm

Rob Henson

9/4/2013

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

Andreamary

9/4/2013

ASKER

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

Peter Kwan

9/5/2013

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

9/5/2013

The AF can be initiated through vba if so required.

Thanks
Rob

Andreamary

9/5/2013

ASKER

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

Rob Henson

9/5/2013

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

9/6/2013

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Andreamary

9/6/2013

ASKER

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

Andreamary

9/6/2013

ASKER

Works perfectly. Thanks so much!