advanced filter turns off my advanced filter

[luke w]

I am having trouble maintaining my autofilter feature in my macro. My goal is to sort lots of rows and columns based on a particular input option or checkbox selected. I have either an option box or a checkbox for every column of data I would like to sort. The macro for each box has an autofilter which filters based on "Yes" or "No", then an advanced filter feature which is supposed to select the column of data I would like to copy and paste elsewhere, based on unique data. I hope I haven't confused anyone yet. So when the advanced filter is activated then it disrupts the autofilter. The problem with this is that the user won't be able to further filter the columns of data through the use of the option or check boxes. I think it would be easier to send someone the macro file so that they can look at it's enirity. So if your really interested in helping me out I would be more then happy to email the file...here is a portion of the code I'm having trouble with.

'this filters the bend material
Private Sub chkbend_Click()
Range("S:S").Clear
Range("T:T").Clear
If chkbend.Value = True Then
Sheets("Sheet3").Range("F1").AutoFilter _
Field:=6, _
Criteria1:="y"
Sheets("Sheet3").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Range(Range("R2"), Range("R2").End(xlDown)).Select
Selection.Copy
Range("S2").Select
ActiveSheet.Paste
Range(Range("S2"), Range("S2").End(xlDown)).Select
Selection.AdvancedFilter Action:=xlFilterCopy, _
copytorange:=Range("T2"), Unique:=True
Range("T2", Range("T2").End(xlDown)).Select
Set Rg = Selection
Application.CutCopyMode = False
Selection.Sort Key1:=Range("T2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End If
Range("T2", Range("T2").End(xlDown)).Select
Set Rg = Selection
With lstbxRESULTS
.ColumnCount = 1
.RowSource = Rg.Address
.ColumnHeads = False
End With
Sheets("Sheet3").Select
Range("A1").Select
End Sub

_________________
Thanks
Luke
This message was edited by luke w on 2002-11-12 18:09

 

[kieran]

For what it is worth..

It may be easier if you copied the visible cells form the autofilter to another sheet and did the filter/unique processing on the new list in the new sheet. Excel is probably turning off the autofilter as you are trying to paste a range to T2 where some of the rows are hidden,( ie a non-contiguous range).

You should be able to do this will little addtioanal coding, however be sure that you clear the new shhet each time before you use it.

HIH