I am attempting to pull data that meets certain criteria out of a
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 that
criteria, copied out of the spreadsheet and then imported into a new
blank spreadsheet. So the new spreadsheet will only contain records
that match the city I specify.

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

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




--
SirDomino

The macro recorder can be your friend in learning how
Record a macro while using
datafilterautofilterfilter on the citycopypaste

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"SirDomino" wrote in message
...

I am attempting to pull data that meets certain criteria out of a
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 that
criteria, copied out of the spreadsheet and then imported into a new
blank spreadsheet. So the new spreadsheet will only contain records
that match the city I specify.

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

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




--
SirDomino

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

On Mar 21, 9:17*am, SirDomino <SirDomino.
wrote:
I am attempting to pull data that meets certain criteria out of a
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 that
criteria, copied out of the spreadsheet and then imported into a new
blank spreadsheet. *So the new spreadsheet will only contain records
that match the city I specify.

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

I would appreciate any help you guys could give. *I look forward to
your responses. *Thanks!

--
SirDomino

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...

The macro recorder can be your friend in learning how
Record a macro while using
datafilterautofilterfilter on the citycopypaste

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"SirDomino" wrote in message
...

I am attempting to pull data that meets certain criteria out of a
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 that
criteria, copied out of the spreadsheet and then imported into a new
blank spreadsheet. So the new spreadsheet will only contain records
that match the city I specify.

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

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




--
SirDomino

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"SirDomino" wrote in message ...

I am attempting to pull data that meets certain criteria out of a
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 that
criteria, copied out of the spreadsheet and then imported into a new
blank spreadsheet. So the new spreadsheet will only contain records
that match the city I specify.

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

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




--
SirDomino

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

"JP" wrote:

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

On Mar 21, 9:17 am, SirDomino <SirDomino.
wrote:
I am attempting to pull data that meets certain criteria out of a
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 that
criteria, copied out of the spreadsheet and then imported into a new
blank spreadsheet. So the new spreadsheet will only contain records
that match the city I specify.

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

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

--
SirDomino