Hi Jonathan,

Am Mon, 1 Apr 2013 10:45:08 +0000 schrieb JonathanK1:

For example, the input box is year but when I enter the year (e.g.,
2007), it copies the rows with 2007 in column 8 but also brings me back
other years as well (2006 rows gets mixed in). Is there a way to keep
this from happening? it should only be searching row 8, not the other
rows (and I can't see anything remotely close to these numbers in any
other cells anyway). It's not that helpful if it keeps doing this (and
it happens every single time I query).

filter column 8 by the answer, copy the visible cells

Sub Filter()
Dim Answer As Integer
Dim LRow As Long

With Sheets("Sheet1")
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
Answer = Application.InputBox("Select a year", "Year", Type:=1)
If Answer = False Then Exit Sub

With .Range("A1:K" & LRow)
.AutoFilter Field:=8, Criteria1:=Answer
.SpecialCells(xlCellTypeVisible).Copy _
Sheets("Sheet2").Range("A1")
End With
.AutoFilterMode = False
End With
End Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2