Selecting using autofilter
 

I've got a spreadsheet and on one of the sheets within it there is some data
I want to move about depending on the result of an autofilter. Basically I
have a list of applicant information and the last column is a date filter
(that shows the month and year) e.g:

Name Address Age Date Filter
Joe Here 21 72004
Fred There 37 72004
Anne Everywhere 26 82004

The other sheets within the spreadsheet are copies of the data in the first
sheet seperated by month. The code I am after is for the filter to select the
date (e.g. 72004) and then place that into the relevant sheet (e.g. Jul 04).
I have managed to get the code to do this until there is no data for one of
the months e.g. using the data aboce if I was to select 92004. When I do this
it just selects all the data and pastes it into the sheet.

Is there anyone who can help me?

Selecting using autofilter
 

Sub Macro1()
Dim sNum As String, sSh As String
Dim rng As Range, i As Long, rng1 As Range
For i = 1 To 12
sNum = CLng(i & "2004")
sSh = Format(DateSerial(2004, i, 1), "mmm yy")
With ActiveSheet.AutoFilter.Range
.AutoFilter Field:=4, Criteria1:=sNum
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1)
End With
Set rng1 = Nothing
On Error Resume Next
Set rng1 = rng.Columns(1).SpecialCells(xlVisible)
On Error GoTo 0
If Not rng1 Is Nothing Then
rng.Copy Destination:=Worksheets(sSh) _
.Cells(Rows.Count, 1).End(xlUp)(2)
End If
Next i

End Sub

--
Regards,
Tom Ogilvy



"cdb" wrote in message
...
I've got a spreadsheet and on one of the sheets within it there is some
data
I want to move about depending on the result of an autofilter. Basically I
have a list of applicant information and the last column is a date filter
(that shows the month and year) e.g:

Name Address Age Date Filter
Joe Here 21 72004
Fred There 37 72004
Anne Everywhere 26 82004

The other sheets within the spreadsheet are copies of the data in the
first
sheet seperated by month. The code I am after is for the filter to select
the
date (e.g. 72004) and then place that into the relevant sheet (e.g. Jul
04).
I have managed to get the code to do this until there is no data for one
of
the months e.g. using the data aboce if I was to select 92004. When I do
this
it just selects all the data and pastes it into the sheet.

Is there anyone who can help me?