Originally Posted by GS[_2_]

Oops.., I forgot you want to put the data in a new wkb. Revise as shown
below...

Sub CopyYearData()
Dim vData, vDataOut(), vAns, n&, j&, k#, lCols&, lNextRow&

vData = ActiveSheet.UsedRange: lCols = UBound(vData, 2)
vAns = Application.InputBox("Enter the year", Type:=1)
k = WorksheetFunction.CountIf(Columns(8), vAns)

ReDim vDataOut(1 To k, 1 To lCols) '//dimension the 2D output array
For n = LBound(vData) To UBound(vData)
If vData(n, 8) = vAns Then
lNextRow = lNextRow + 1
For j = 1 To lCols: vDataOut(lNextRow, j) = vData(n, j): Next
'j
End If 'vData(n, 8) = vAns
If lNextRow = k Then Exit For '//escape when done
Next 'n
Workbooks.Add
Cells(1).Resize(UBound(vDataOut), UBound(vDataOut, 2)) = vDataOut
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion