VBA to filter a table, with values from cells

[Vladgs]

Hi everyone!

I am trying to write some code to filter a table A20 to F10000, table header is on row 20
I am trying to do the following: I have the the criteria in cell A19 , and the column witch needs to be filtered in cell E17

So for example in E17 I have "A20" and in A19 I have the word "clean" The expected result is filter cell A20 (refered to in cell E17), by the word "Clean", from cell A19.

Any sugestion on how to do this ?

This was my initial approach , but it dose not work.

Sub Filter_Stuff()
 
    With Sheets("pivot")
 
    .Range("A20:F1000").AutoFilter Field:=.Cells("E17").Value, Criteria1:="*" & Cells("A19") & "*"
 
 
 ', Field:=2, Criteria1:=.Range("H6").Value
    End With
 
End Sub

Any help is appreciated

 

[tlowry]

Here is some code that may help...

(You will need to change the bold constants to match your situation.)

Sub test()
    With Sheets("[B]Data[/B]")
        .Range("E19") = "[B]Jones[/B]"
        .Range("E17") = [B]3[/B]
        .AutoFilterMode = False
        .Range("A21:F1000").AutoFilter Field:=.Range("E17").Text, Criteria1:=.Range("E19").Text
    End With
End Sub

 

[Vladgs]

Thanks tlowry, I will have to wait until Monday to do that!

 

[Vladgs]

So I still can't filter the table..

Let's say the criteria is in cell A1 words like "clean" or "Maintenance", and the column I wish to filter by is in cell A2, i can choose to filter by A20 , B20, C20 or E20

I am looking foe a code that picks up values from these 2 cells A1 and A2 to look for a certaion word on a certain column.

Any help is appreciated

 

[Akuini]

Try this:

Sub try1()
 
      ActiveSheet.AutoFilterMode = False
      Range("A20:F1000").AutoFilter Field:=Range(Range("E17")).Column, Criteria1:="*" & Range("A19") & "*"
End Sub

 

[Vladgs]

Hi Akuini,

Unfortunately that code didn't work but I was able to modify something to suite my needs.

Sub Macro6()


ActiveSheet.PivotTables("PivotTable5").ClearAllFilters

Dim score As Integer, result As String
 score = Range("I3").Value

If score = 1 Then
ActiveSheet.PivotTables("PivotTable5").PivotFields("Vendor Name"). _
        PivotFilters.Add2 Type:=xlCaptionContains, Value1:=ActiveSheet.Range("I4")
Else

If score = 2 Then
ActiveSheet.PivotTables("PivotTable5").PivotFields("Prime Account"). _
        PivotFilters.Add2 Type:=xlCaptionContains, Value1:=ActiveSheet.Range("I4")
Else

If score = 3 Then
ActiveSheet.PivotTables("PivotTable5").PivotFields("Item Description"). _
        PivotFilters.Add2 Type:=xlCaptionContains, Value1:=ActiveSheet.Range("I4")
Else
If score = 4 Then
ActiveSheet.PivotTables("PivotTable5").PivotFields("Country"). _
        PivotFilters.Add2 Type:=xlCaptionContains, Value1:=ActiveSheet.Range("I4")

End If
End If
End If
End If

   
End Sub

A bit more primitive but i dose the job.

Thank you for your effort and ideeas!