Formulas

Feb 28th, 2002, 02:30 PM

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. · Feb 28th, 2002, 02:47 PM

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

hennahairgel · Feb 28th, 2002, 03:40 PM

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. · Mar 1st, 2002, 08:24 AM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 28th, 2002, 02:30 PM	#1
Guest Posts: n/a	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?

Feb 28th, 2002, 02:47 PM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	This can be accomplished using an Advanced AutoFilter in a recorded macro.

Feb 28th, 2002, 03:40 PM	#3
hennahairgel New Member Join Date: Feb 2002 Location: Bristol, England Posts: 39	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

Mar 1st, 2002, 08:24 AM	#4
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	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.