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