none
autofilter macro

    Question

  • Hi Guys, 

    I have a scenario where I need to filter one sheet data from another sheet. Please find attached spreadsheet, where I'm looking to highlight sheet1 count column and then click issues button which will then auto filter sheet 2 data and show up sheet 2. The commonality of both sheets is issue key column. I have tried in many ways but couldn't able to achieve it. Any help is highly appreciated. 

    spreadsheet link: http://www.rapidshare.com.cn/PYSt2Vf

    Thanks.


    - please mark correct answers

    Tuesday, March 21, 2017 3:41 AM

All replies

  • Is this the right forum to ask this question?

    - please mark correct answers

    Tuesday, March 21, 2017 2:43 PM
  • Hi,

    You could get selected filter items in sheet1 by checking their Hidden property.

    It is easy to filter table in sheet2 if you have the items array.

    Here is the example.

    Sub IssueClick()
    Dim arr() As String
    Dim rng As Range
    Dim count As Integer
    Dim ws As Worksheet
    count = 0
    Set rng = Worksheets("Sheet1").Range("B5:B9")
    For i = 1 To rng.Cells.count
    If rng.Cells(i, 1).EntireRow.Hidden = False Then
    ReDim Preserve arr(count)
    arr(count) = rng.Cells(i, 1)
    count = count + 1
    End If
    Next
    Worksheets("Sheet2").ListObjects("Table2").Range.AutoFilter Field:=2, Criteria1:= _
            arr, Operator:=xlFilterValues
    Worksheets("Sheet2").Activate
    End Sub

    Best Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 22, 2017 8:03 AM
    Moderator