I'm using auto-filter in my applications in order to filter my data(a name for example).my problem is that i can't figure out a way to create a macro that will help me "pick" the name and copy it to another cell.i'm interested in copying just the criteria and not the whole selection.
Need a better explanation. Do you want an inputbox so a user can enter a name and the database will filter on that name and copy some stuff to another worksheet?
If you want to detect which columns contain a filter and the criteria for the filter, then you can apply the following code (it assumes you have a database with a filter and some criteria in the worksheet 'Sheet1':Code
Sub Test() Dim filterArray() Dim f As Integer Dim currentFiltRange As String Dim w As Worksheet Set w = ActiveWorkbook.Sheets("Sheet1") With w.AutoFilter currentFiltRange = .Range.Address MsgBox currentFiltRange With .Filters ReDim filterArray(1 To .Count, 1 To 3) For f = 1 To .Count With .Item(f) If .On Then filterArray(f, 1) = .Criteria1 If .Operator Then If .Operator = 1 Then filterArray(f, 2) = "AND" ElseIf .Operator = 2 Then filterArray(f, 2) = "OR" End If filterArray(f, 3) = .Criteria2 End If MsgBox "Column " & f MsgBox "Criterium 1 = " & filterArray(f, 1) MsgBox "Operator = " & filterArray(f, 2) MsgBox "Criterium 2 = " & filterArray(f, 3) End If End With Next f End With End With End Sub
I don't know how to trigger an event by just changing the filter criterion. In case you change something on your sheet or in case you select another cell, you can use the workbook sheetchange and workbook sheetselectionchange events to put the "criterion" in the cell you want. Like this: In the ThisWorkbook module, put the following code:Code
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Call UpdateCell15 End Sub Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Call UpdateCell15 End Sub
Then add a general module to the VBAproject and add the following codeCode
Sub UpdateCell15() Dim f As Integer Dim w As Worksheet Set w = ActiveSheet With w.AutoFilter With .Filters For f = 1 To .Count With .Item(f) If .On Then w.Cells(1, 5).Value = Right(.Criteria1, Len(.Criteria1) - 1) End If End With Next f End With End With End Sub
It would be better if cell(1, 5) was automatically filled with the name in your selection, but I can't figure out how this can be done.
Don’t have an account yet? Register yourself now and be a part of our community!