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]

It doesn't seem to be working, but I've never used VBA before in my life. Is it possible for me to upload my file so you can see it?

 

[Peter_SSs]

It doesn't seem to be working,

In that event it is always best to describe in what way it is not working, so that helpers have something to go on.
- Does it crash Excel?
- Does it give an error message? (What message on what line)
- Does it do nothing?
- Does it bring the wrong results (what results did it give for the sample data?)
- etc

Is it possible for me to upload my file so you can see it?

Excel files cannot be uploaded to this forum. They can be uploaded elsewhere (DropBox, OneDrive etc) with a link provided but helpers may choose not to go to another site to do the download or may be prevented from doing so by their workplace security restrictions. In most cases it is better to provide small samples (of small sections of a couple of the worksheets, not all 20 or so) using the option suggested in my signature block below.

I tested with these 3 sheets

Book1
	A	B	C
1	Name	Amount	Status
2	John	150,000	Approved
3	Chris	350,000	Pending
4	Steven	215,000	Funded
5	Shawn	320,000	Pending
6	James	125,000	Funded
Sheet1

Book1
	A	B	C
1	Name	Amount	Status
2	John	200	Funded
3	Ann	300	Funded
4	Jeff	400	Funded
Sheet2

Book1
	A	B	C
1	Name	Amount	Status
2	Ben	3	Approved
3	Dan	2	Pending
4	Ann	6	Pending
5	Jen	9	Pending
6	Jill	5	Funded
Sheet3

.. and this is the result of the code. Is that not what you want?

Book1
	A	B	C
1	Name	Amount	Status
2	Chris	350000	Pending
3	Shawn	320000	Pending
4	Dan	2	Pending
5	Ann	6	Pending
6	Jen	9	Pending
Summary

 

[Steven975]

Yes, you have replicated my needs exactly, I just have no idea how to implement it.

When I say it doesn't work I mean it did nothing, or at least I didn't notice anything.

Would all sheets need to be the same, except the summary? I have a few sheets that I am using to write notes and ideas and store lists and such. Would that be getting in the way at all?

Here is my file, uploaded to Google drive if that is easier than trying to communicate with such an amateur!

 

[Peter_SSs]

.. uploaded .. if that is easier than trying to communicate with such an amateur!

I'd rather try to just do it in the forum as the thread will then be more use to future readers as well.

To implement ..
1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Press Alt+F8 to bring up the Macro dialog
6. Select the macro & click ‘Run’
7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Would all sheets need to be the same, except the summary?

Unless there are a lot of them and they are large or might still have 'Pending' or whatever you are searching for in column C then it shouldn't matter.
However, if you wanted to exclude them from the code then we need some way to identify either the sheets to process or the sheets to exclude. That could be by where the sheets are (eg exclude the left hand 3 sheets) or what the sheets are called (eg process all sheets whose names start with "data")

 

[Steven975]

Peter, you're too kind. This worked when I replicated your exact sheets, but now I need to tweak them slightly. My first row of data starts in cell A56 and goes to Z56 (headers in 55). Which pieces of the code do I adjust to make that fit?

How would I adjust it so it searches for "Pending" AND "Approved", if the content in the F column contains "Mortgage"?

 

[Peter_SSs]

To be sure, could we have one small sample worksheet and the corresponding Summary sheet results (with XL2BB so it is easily copied)?

 

[Steven975]

Unfortunately I can't download XL2BB on my work computer. Will a screenshot be helpful?

 

[Steven975]

This might be higher resolution for you.

 

[Peter_SSs]

The resolution is still no good & I still would have to do a lot of manual typing.

You can also just copy a range from Excel and paste here (& tell us what range it is), like this range A1:C6

But please try to keep it fairly small and only relevant parts.

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

 

[Steven975]

I'll see if I can use XL2BB at home tonight.

Should I still try to limit the range with that?