Formulas

[Guest]

We have a master spreadsheet that we download from a call center that lists all people who called, addresses, telephone number and comments. We sort by state. I would like to make Excel pull all the people from Vermont and copy to another page, all people from New Jersey and copy onto a separate page, etc ... in one shot. Any suggestions?

 

[Mark W.]

This can be accomplished using an Advanced AutoFilter in a recorded macro.

 

[hennahairgel]

The autofilter function is very useful. However, if you store the address as a single cell value be slightly more cunning and filtet on a containing ie for vermont
Selection.AutoFilter Field:=1, Criteria1:="=vermont", Operator:=xlAnd
you can then paste and copy in ther normal way. if you send me a few lines of the data i can write the whole thing for you for free.
Just tell me what sheets you want the data to go to, and if the sheets exist.
ciao,
Henry

 

[Mark W.]

Copy/Paste is a very inefficent way of transferring large amounts of data. As I was suggesting above this can be accomplished with a single VBA statement...

Range("Sheet1!A1:C5").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("Sheet1!F1:F2"), CopyToRange:=Range("Sheet2!A1:C1"), Unique:=False

Here, a 3 field, 5 row data list is filtered on a single column criteria (F1:F2) and the results are "sent" directly to Sheet2.