Find text in single column and copy selected cells from same row in next empty row of different worksheet

withquestions

New Member
Joined
Sep 8, 2014
Messages
1
I believe the title is fairly descriptive but the scenario is that I need to find a formula answer "yes" in a certain column from an origin worksheet, then copy selected cells from the same row into a next blank row and different columns of a destination worksheet within the same workbook, looping through every "new" "yes" appearing in the same column of the origin sheet. The purpose of this is to use the formula to determine when an agreement has gone through a finalization process and is ready to be moved from the Pending Documents into the Active Contracts category and worksheet. Finally, I would like the copied row to reflect "yes" again upon completion of the copying process onto the new worksheet, change the background color from default (white) to a dark gray, thus diminishing its prominence and for that text/change to exclude it from being copied into Active Contracts again. The sheet will change and will need to be updated on a daily basis, and there are sometimes 30-40 changes in a single day, which is laborious and unnecessary for such a repetitive task. I have attached an image of the before for both pages, and the desired effect.

Obviously, the find text is to operate on Origin sheet column N when text is "YES" and below row 3 (header rows 1-2), and then to copy the Destination sheet the name (column A to column A), client code (M to B) sign date (G to C), and salesperson (C to H). Then, it would change the entire row of the copied entry to gray background fill and fill in "YES" at column O indicating its completion (note that I cannot locate a tool to change the fill color in the post, so I am using red text as substitution). This appears to be the best organization of information for using the data, but if it is insurmountable, I could move columns, although I am hoping not to do so. Please note that while I have searched this forum and tried to extract solutions, I cannot find enough of an answer to be workable, as it appears that many people want to copy complete rows or want to add a new sheet for data rather than add to an existing sheet. Oh yes, this is Windows 7, Excel 2013.

Thank you enormously for sharing your mental acumen and training with a struggling learner!


Origin sheet before


PENDING DOCUMENTS
NameDate ReceivedSalespersonDate of Salesperson ApprovalStandard or Specifically ApprovedComments Re: ForwardingDate Forwarded for Management SignatureDate Signed by ManagementDate of Client Signature or Forwarded for Client SignatureSign Date (Date Returned With All Signatures)Date Forwarded to Data EntryOriginal Document Returned for FilingClient CodeMove to Active ContractsCompleted / Copied to Active ContractsFollowup Date
ABC Company8/1/2014JS8/1/2014ApprovedApproved8/1/20148/1/20147/30/20148/1/20148/1/20148/1/20141234YES
XYZ Company8/1/2014JD8/1/2014StdStd8/1/20148/1/20148/1/2014FALSE

<tbody>
</tbody>
Destination sheet before

ACTIVE CONTRACTS
MASTER CONTRACTS ABSTRACTClient CodeSign dateStart dateExpiration dateStateEstimated volumeSales PersonAutomatic extensionsPrimarySecondaryTertiaryDefaultLate StageLitigationLoaded in SystemDate of System Entry30 day check120 day reviewRate TermsSettle-ment TermsSpecial Reporting/ Remittance RequirementsCredit BureauLegalBank-ruptcyProbate
AZ Corporationaz1237/1/20147/1/20146/30/2015ST$ 10,000.00JDN/AXY7/2/20148/6/201410/30/2014Standard and customary10%NoneYNNN

<tbody>
</tbody>

<tbody>
</tbody>




Origin sheet after/desired


PENDING DOCUMENTS
NameDate ReceivedSalespersonDate of Salesperson ApprovalStandard or Specifically ApprovedComments Re: ForwardingDate Forwarded for Management SignatureDate Signed by ManagementDate of Client Signature or Forwarded for Client SignatureSign Date (Date Returned With All Signatures)Date Forwarded to Data EntryOriginal Document Returned for FilingClient CodeMove to Active ContractsCompleted / Copied to Active ContractsFollowup Date
ABC Company8/1/2014JS8/1/2014ApprovedApproved8/1/20148/1/20147/30/20148/1/20148/1/20148/1/20141234YESYES
XYZ Company8/1/2014JD8/1/2014StdStd8/1/20148/1/20148/1/2014FALSE9/1/2014

<tbody>
</tbody>

Destination sheet after/desired


ACTIVE CONTRACTS
MASTER CONTRACTS ABSTRACTClient CodeSign dateStart dateExpiration dateStateEstimated volumeSales PersonAutomatic extensionsPrimarySecondaryTertiaryDefaultLate StageLitigationLoaded in SystemDate of System Entry30 day check120 day reviewRate TermsSettle-ment TermsSpecial Reporting/ Remittance RequirementsCredit BureauLegalBank-ruptcyProbate
AZ Corporationaz1237/1/20147/1/20146/30/2015ST$ 10,000.00JDN/AXY7/2/20148/6/201410/30/2014Standard and customary10%NoneYNNN
ABC Company12348/1/2014JS

<tbody>
</tbody>
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,117
Messages
5,857,479
Members
431,882
Latest member
saaaaaaaaaaaaaaaaaaaaaa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top