Find, copy and paste

[Audissimo]

Hi!

I have a workbook which has a few worksheets. Now, the first sheet I have a vendor list as a following:

A B
1 VENDOR CONTR.STATUS
2 Vendor 1 Expired
3 Vendor 2 Expires in ...days
4 Vendor 3 Expires in ...days
.
.
.
500 Vendor 500

Now, I need to create a report, to another worksheet, that tells which vendors are expired. So I need to find them, copy them and paste them to another worksheet. How can I manage it?

 

[GlennUK]

I'd do it by performing an Advanced Filter, filtering out those with Expired in column B. If you perform this in a macro, you can specify a separate sheet for the output.

Here's an example ( slightly different criteria to yours, but you'll get the idea ):
thread707-934163


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Loomah]

Hi
this is probably something you could have worked toward using Filters and the macro recorder but....This is the kind of thing you could be looking at

With Worksheets("Sheet1").Range("A1")
    .AutoFilter Field:=2, Criteria1:="Expired"
    .CurrentRegion.Copy Worksheets("Sheet2").Range("A1")
    .AutoFilter
End With

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Loomah]

Ah, answer's already there!

But am I missing something? XL97 wouldn't allow me to use advanced filter to copy to another sheet. Is the option available in later versions?

I almost feel like a beginner again!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[GlennUK]

Loomah, you know you can use advance filter to copy to another sheet in XL97 if you put it in a VBA routine, don't you?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Loomah]

Glenn
I thought I did but apparently not as I couldn't get it to work.

I have a strange feeling there's something odd about the set up here as it wouldn't be the first time I've tried something that should work and it hasn't! It'll probably work in the morning though!!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​