Pull some rows from one worksheet and compile into another

[Steven975]

I'm looking for a way to automatically take anything with the status "Pending" and compile a list of them on a seperate another worksheet. Any help is much appreciated!

Worksheet One (INPUT)

Name	Amount	Status
John	150,000	Approved
Chris	350,000	Pending
Steven	215,000	Funded
Shawn	320,000	Pending
James	125,000	Funded

Worksheet Two (OUTPUT)

Chris	350,000	Pending
Shawn	320,000	Pending

 

[Steven975]

My desired outcome:
If the F column contains the word "Mortgage" and the status is "Pending" or "Approved" I want the entire line to move to my summary sheet titled "Pending"

Book1
	A	B	C	D	E	F	G	H	I	J
55	Date	Account #	Name	Member #	Main	Type	Status	Dollar Amount	Refinanced Amount	NEW FUNDS
56	2019-12-03	999123456	Steve	123	Lending	Fixed Rate Loan	Funded	$10,000		$10,000
57	2019-12-06	999123457	Steven	124	Lending	Promotional Mortgage	Funded	$150,000	$75,000	$75,000
58	2019-03-03	999123458			Lending	Mortgage	Preapproved	$150,000
59	2019-05-06	999123459			Lending	Broker Mortgage	funded	$200,000		$200,000
60	2019-05-07	999123460			Lending	Mortgage	approved	$350,000
61	2019-07-06	999123461			Lending	Mortgage	pending	$434,000
62					Deposits	GIC	Complete	$15,000
Staff 2

 

[Steven975]

Any thoughts on this?

 

[Peter_SSs]

Any thoughts on this?

Sorry - busy holiday season.

This is quite a change in layout & requirement so try this very different approach. It assumes sheet 'Pending' exists and contains headings.

Sub Status_Pending_Approved()
  Dim ws As Worksheet, wsP As Worksheet
  Dim lr As Long, nr As Long
  Dim rCrit As Range
  
  Set wsP = Sheets("Pending")
  For Each ws In Worksheets
    If ws.Name <> "Pending" Then
      lr = ws.Range("F" & ws.Rows.Count).End(xlUp).Row
      If lr > 55 Then
        nr = wsP.Range("G" & wsP.Rows.Count).End(xlUp).Row + 1
        Set rCrit = ws.Range("AZ1:AZ2")
        rCrit.Cells(2).Formula = "=AND(F56=""Mortgage"",OR(G56=""Approved"",G56=""pending""))"
        ws.Range("A55:Z" & lr).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rCrit, CopyToRange:=wsP.Range("A" & nr), Unique:=False
        wsP.Rows(nr).Delete
        rCrit.ClearContents
      End If
    End If
  Next ws
End Sub

If the F column contains the word "Mortgage"

BTW, this isn't clear as it doesn't say whether column has to contain "Mortgage" and nothing else (like cell F58 in your sample) or whether "Mortgage" anywhere in the cell (like F57 and F59) should also be considered. My code assumes "Mortgage" and nothing else.

 

[Steven975]

Hi Peter, I apologize, I realized I came off sounding like a bit of a jerk on my Dec 28th post. I appreciate your help so much! I'm learning a lot, and thought originally the code would be easier to tweak to suit my purposes, but this unfortunately goes way over my head!

This project has changed a couple times since I last posted, so I thought it would be good to wait till the final layout was a bit more nailed down.

This is the main tracking sheet, where our lenders will input their new loans, deposits, phone calls, etc. There will be three separate workbooks (one for each branch) with separate worksheets for each staff.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
58	Date	Account #	Name	Member #	Main	Type	Status	Dollar Amount	Refinanced Amount	NEW FUNDS	Prime +	Branch	Policy Exception	NOTES	Disbursed By	Possession Date	Date Funded	Term Length	Broker Name	Broker Fee	Broker Date Paid	MONTH	Quarter	Interest Earned
112	2019-09-09	110690030590	Member 1108	1108	Call	Pending	Contacted					Kildare										Sep	Q3
113	2019-09-12	110690037088	Member 897	897	Call	Opportunity Manager	LMTC					Kildare										Sep	Q3
114	2019-09-13	110690078489	Member 850	850	Call	Pending	CU determined no contact					Kildare										Sep	Q3
115	2019-09-15	110690046566	Member 1063	1063	Call	Pending	CU determined no contact					Kildare										Sep	Q3
116	2019-09-16	110690033357	Member 929	929	Call	Pending	LMTC					Kildare										Sep	Q3
117	2019-09-19	110690017271	Member 1335	1335	Call	Pending	LMTC					Kildare										Sep	Q3
118	2019-09-20	110690046816	Member 830	830	Call	Pending	Member contacted CU					Kildare										Sep	Q3
119	2019-09-22	110690026900	Member 1435	1435	Call	Pending	Contacted					Kildare										Sep	Q3
120	2019-09-25	110690059771	Member 1234	1234	Outage	MSR		$15.00		$15.00		Kildare										Sep	Q3
Staff 1

Then in a separate workbook we will have all of our reports generate. This report is called 'Pending' so we know how much money we will need for upcoming mortgages. On the 'Pending' worksheet there is three reports, one for each branch. Branch One (Kildare) starts in A1. Branch Two would start to the right of Branch One.

I'm looking to make something that will go through all three branch workbooks and through each staff's tracking sheet to pull out anything that contains the word 'Mortgage' (including things like 'Broker Mortgage') in Column F, if Column G is 'Approved' or 'Commitment Signed' or 'Pending', when Column L is equal to the branch name on the report page (A1 for Branch One).

My plan is to have a button to update each branch individually. I think that would make the code easier, and allow the different branch managers to update their reports only when they wanted the new numbers.

I don't need every column from the Tracking Sheet, but I imagine it's easier to just pull the entire row, correct? My thought here was to pull the entire row over and have the unnecessary columns hidden.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Kildare							2020-02-08
2	Report Date:	2020-02-08						9:45:57 PM
3
4	Member Name	Type	Status	Funding Date	Possession Date	Total Mortgage	Refinanced	New Funds	Term Length	Builders Advances	Rate
5	Member 1	Broker Mortgage	Preapproved			$200,000		$200,000
6	Member 2	Builders Mortgage	Builder's - Advancing			$484,500	$331,100	$153,400
7	Member 3	Mortgage	Preapproved			$125,000		$125,000
8	Member 4	Mortgage	Approved	2020-01-20		$120,000		$120,000
Pending