Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP: Selecting Non-Visible (Hidden) Cells when AutoFilter is Appl | Excel Worksheet Functions | |||
HELP: Selecting Non-Visible (Hidden) Cells with AutoFilter Applied | Excel Discussion (Misc queries) | |||
HELP: Selecting Non-Visible (Hidden) Cells with AutoFilter Applied | Excel Discussion (Misc queries) | |||
Selecting Filtered records only when AutoFilter is on | Excel Programming | |||
Selecting Range After AutoFilter | Excel Programming |