Using data in Column for criteria in Filter (VBA)

[SToRIB]

Hi everyone,

First time poster, long time follower. I have been struggling hard with a particular VBA code.

I'm trying to take a column of data on a sheet (sheet1) and use it as filter criteria for a specific column on another sheet (Q1 2009) within the same workbook. I have code below that will cycle through each value with in the column and enter it into the criteria for the column on the filter sheet but it never retains the filter and just jumps to the next criteria so that the end result is Q1 2009 sheet is filtered by only the value from the last value within the column.

Can someone assist me in having the criteria the filter should use from worksheet "Sheet1" filter the data on "Q1 2009" worksheet? What I have so far is below:

Sub FilterListOrTableData()
    Dim ACell As Range, ARange As Range
    Dim FilterCriteria As String
    
    Application.ScreenUpdating = False

    'Check to see if the worksheet is protected.
    If ActiveSheet.ProtectContents = True Then
        MsgBox "This macro will not work when the worksheet is write-protected.", _
               vbOKOnly, "Filter example"
        Exit Sub
    End If

    Set ARange = Worksheets("Sheet1").Range("A1:A316")
    For Each ACell In ARange
    Worksheets("Q1 2009").ListObjects("Table1").Range.AutoFilter Field:=6, Criteria1:=ACell.Text
    Next ACell
    
    
End Sub

 

[AlphaFrog]

It's not clear what you want to do each time the data is filtered? After you filter the data, you need to do something with it.

    [color=darkblue]For[/color] [color=darkblue]Each[/color] ACell [color=darkblue]In[/color] ARange
    Worksheets("Q1 2009").ListObjects("Table1").Range.AutoFilter Field:=6, Criteria1:=ACell.Text
        [color=green]'Do somtheing with each filtered data here[/color]
        [color=green]'[/color]
        [color=green]'[/color]
    [color=darkblue]Next[/color] ACell

 

[SToRIB]

I apologize for not being more detailed.

I want the criteria to be saved so that when the macro is finished, all the values from the column on sheet1 are the criteria for the filter on "Q1 2009."

Basically, I want the macro to go through column F on "q1 2009" and "check the box" on values that are in the column on sheet1.

 

[AlphaFrog]

Try this. I don't have the proper version of Excel to test this. It's likely their may be something wrong in the code, but maybe it will point you in the right direction. I found the code syntax here. http://blog.contextures.com/archives/2010/12/15/excel-autofilter-with-criteria-in-a-range/

I suspect if there is a value in the criteria range that is not in the filter list, the code would error.

Sub FilterListOrTableData()

    Dim vCrit As Variant
    
    'Check to see if the worksheet is protected.
    If ActiveSheet.ProtectContents = True Then
        MsgBox "This macro will not work when the worksheet is write-protected.", _
               vbOKOnly, "Filter example"
        Exit Sub
    End If

    vCrit = Worksheets("Sheet1").Range("A1:A316").Value
    Worksheets("Q1 2009").ListObjects("Table1").Range.AutoFilter Field:=6, _
                                                                 Criteria1:=Application.Transpose(vCrit), _
                                                                 Operator:=xlFilterValues
    
    
End Sub

 

[SToRIB]

Thanks for the quick response, but it didn't look like anything was showing up in the filter, either -- even though I know some of the values on Sheet1 exist on the table on "q1 2009". Any other tricks up your sleeve?