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>
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Watch MrExcel Video

Forum statistics

Threads
1,118,809
Messages
5,574,435
Members
412,592
Latest member
moonsugar
Top