importing only a few records from one sheet to another...

[snapper97]

I apologize in advance if this was answered in another post.

I have a worksheet that contains about three hundred records and has about four records added each week. Only two or three a month meet a certain criteria. I would like to have those records automatically added to its own seperate sheet.

For example:
(ignore the dots, just for spacing)
Where anything in column "A" is lower than 5, copy the entire record to another worksheet or workbook.

.......A....B....C....D....E....F....
1....15...2....6....19...12...5...
2....3.....1....21...4....10...8...
3....14...4....7.....11...9....11
4....12...6....8.....10...8....23
5....2.....7....20...3.....10..5
6....15...1....6.....17...9....8

My boss wants to just click on the "less than 5" tab and have them automatically there. Thanks so much for you help.

 

[Tazguy37]

Re: importing only a few records from one sheet to another..

You could use Data, Filter, Autofilter, Custom, and select your criteria. If you want this data on another sheet, you could copy it, and PasteSpecial, Values. If you need this to be a macro or automated procedure, post back and I'm sure someone can come up with code to do this. Hope that helps!

EDIT:
Unfortunately, Autofilter wouldn't work too well if you don't have column headings. If that's not an option, please indicate that.

 

[snapper97]

Re: importing only a few records from one sheet to another..

Yes, automated. I already drop by her office FAR too often, and I would like to make it as unintensive as possible.

Thanks for the help, though.

 

[KenWright]

So set up an Advanced filter on sheet2, using a dynamic range referring to the data on sheet1. Then just have a button to press to refresh the filter, or even tie it to the worksheetchange event or something like that. That way it won't be formula intensive either.

 

[KenWright]

Define the Name 'Database' as

=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

Then on sheet2 set up the ranges as needed and define the Criteria range as 'Criteria' and the Extract range as 'Extract'

Now just put a button on the sheet and refer to the following code:-

Sub FilterData()
    Range("Database").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
        ("Criteria"), CopyToRange:=Range("Extract"), Unique:=False
End Sub

 

[snapper97]

Re: importing only a few records from one sheet to another..

Thank you, Ken for your help. I hope this does this trick. I'll try to apply this as best I can. Honestly, I've never tried anything this dynamic with excel.

Wish me luck.