How do I pull certain data out of spreadsheet and import into another?

[SirDomino]

I am attempting to pull data that meets certain criteria out of
spreadsheet and have it imported into a blank spreadsheet.

I have 5,000 records in Excel 2003 with fields for the Company Name
Address, City, State, and Zip.

I would like to specify a city and have all the records that match tha
criteria, copied out of the spreadsheet and then imported into a ne
blank spreadsheet. So the new spreadsheet will only contain record
that match the city I specify.

I have been trying to do this for days, and I searched the forum
without any luck.

I would appreciate any help you guys could give. I look forward t
your responses. Thanks

 

[Don Guillett]

The macro recorder can be your friend in learning how
Record a macro while using
data>filter>autofilter>filter on the city>copy>paste

 

[JP]

In addition to Don's suggestion, Ron de Bruin has an add-in that can
do this.

See http://www.rondebruin.nl/easyfilter.htm

Also I am working on a similar tool:

http://tinyurl.com/3c3tyv


HTH,
JP

 

[Don Guillett]

An example. See below for the refined version
Sub Macro5()
'
' Macro5 Macro
' Macro recorded 3/21/2008 by Donald B. Guillett
'

'
Range("A6:H6").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="mycriteria"
Range("A14:H40").Select
Selection.Copy
Sheets("destinationsheet").Select
Range("A19").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A28").Select
Sheets("sourcesheet").Select
Selection.AutoFilter
Range("A9").Select
End Sub

Sub refined()
with sheets("sourcesheetnamehere")
.Range("A6:H6").AutoFilter Field:=2, Criteria1:="mycriteria"
.Range("A14:H40").Copy Sheets("destinationsheetname").Range("A19")
.Range("A6:H6").AutoFilter
end with
End Sub

 

[Ron de Bruin]

See also
http://www.rondebruin.nl/copy5.htm

 

[thefonz37]

That's a cool add-in, thanks for posting it. Is there a way to have the
criteria reference a cell?