How do i filter a range in another sheet with VBA without activating the sheet

Question

Good day,

I am having problems with Set Ranges and it has been quite frustrating when using set ranges from non-active sheets.

The problem is:

I have a sheet called "Dashboard". In this sheet i have a Listbox that when selected will filter values (based on listbox.column value) on a Table in another sheet called "Budget". However, i get Error 1004 (Autofilter method of Range class failed), after closing the error it filters the range. So it seems it works somehow, however it gives me error.

The code below is the one i'm using to filter the range. It is inserted in the "Dashboard" Sheet object.

Private Sub DashboardBudgetlst_Change()

Dim rng As Range
Dim i As Integer

  i = Me.DashboardBudgetlst.ListIndex

    If i >= 0 Then
     If Me.DashboardBudgetlst.Selected(i) And Me.DashboardBudgetlst.Column(0, i) <> "" Then
          Set rng = Budget.Range("B1:E" & lrow(Budget, "A"))
          rng.AutoFilter 1, Me.DashboardBudgetlst.Column(1, i)
          Set rng = Nothing
     End If
   End If

End Sub

The macro will filter a range that is used for a chart, therefore will filter the values of my chart. Also i don't want to use pivot tables as it is very slow.

Further exploring the question. How can i use Ranges from one Worksheet that are Set in another Worksheet without having to Activate the Sheet of that range? (most of the time i have to do Sheet.Activate before using the Set range for that sheet).

Would you guys know the workaround and why there is this problem with Set Ranges?

I know there are similar questions about ranges, but none with the same specifications.

Additional Information (Edit):

1- Error is on line:

 rng.AutoFilter 1, Me.DashboardBudgetlst.Column(1, i)

2- Listbox index >= 0 to ensure that listbox is not empty and there's an item selected. When a listbox is empty the listindex = -1.

3- lrow(Budget, "A") calls the following function to get the last row in the specified sheet:

   Function lrow(SH As Worksheet, col As String)
        lrow = SH.Cells(Rows.Count, col).End(xlUp).Row
   End Function

4- With msgbox rng.address just before the error line, i receive $B$1:$E$5 as the address.

5- I did a temporary workaround using

On Error Resume Next

6- Value for Me.DashboardBudgetlst.Column(1, i) is a keyword to be filtered and depends on the selection. The listbox is fed with the same range that i am filtering. So i am selecting the column "1" from the list which is under the header "Item". When i select something from the listbox i want it to filter by that Budget Item, sometimes can be "Accommodation" or anything else i have there.

7- Debug.Print on :

Debug.Print rng.AutoFilter; 1, Me.DashboardBudgetlst.Column(1, i)

Selected on Travel Expenses in listbox Returns on Immediate Window:

True 1        Travel Expenses

8- Some Screenshots:

Listbox in Dashboard Sheet (Excel View)

Range in Budget Sheet (Excel View)

Objects being used (VBA view)

It works as after i closed the error the filter would apply. However i would like to know if there's another workaround and i'm not sure about using "On Error Resume Next" (Is it bad for your code?)

On which line do you get the error? And how do you "close the error"? Also, what would llstIndex be if not >=0? Is there a -1? — ashleedawg
There were other questions. ...and now two more: what's lrow? And what's the value of rng.Address just before the error? (Find out by adding a line right before the error: MsgBox rng.Address) — ashleedawg
What is the value of this Me.DashboardBudgetlst.Column(1, i)? It looks like you are setting the proper ranges to the other sheet, if the sheet Code Name is indeed "Budget" — paul bica
@paulbica i edited the question and answered you in point 6. And the name of the Sheet Object (in the VBA) is Budget indeed. It is quite odd this kind of error seems to happen often to me when i am using a set range from another sheet without activating the second sheet in the range. The filter procedure is in the sheet Dashboard code and filters a range in the Budget sheet. Any idea? — Raphael Yaghdjian
The most common problem with working with ranges from other sheets without selecting or activating them is that the ranges are not explicitly qualified: Range("A1") could be on any of the sheets but if it's not qualified the Activesheet will be used by Excel by default (the sheet visible on the screen. You do qualify your range properly as in Budget.Range("B1... which is great! On Error Resume Next is a bad specially for beginners because you don't understand or know the errors you are hiding - so your motivation is great as well! — paul bica

paul bica paul bica · Accepted Answer · 2018-04-09T11:42:54

I was able to duplicate the error

Depending on the number of items select in the ListBox, the issue seems to be that there are multiple _Change events being triggered

I was able to stop the error by using an event flag

Option Explicit

Private Sub DashboardBudgetlst_Change()
    Dim rng As Range, i As Long, lstItm As String, crit As String, startIndex As Long

    If Application.EnableEvents = False Then Exit Sub    'If flag is Off exit Sub

    Application.EnableEvents = False    'Turn flag Off
    With Me.DashboardBudgetlst
        i = .ListIndex
        If i >= 0 Then
            If .Selected(i) And .Column(0, i) <> "" Then

                Set rng = Budget.Range("B1:E5") ' & lrow(Budget, "A"))
                rng.AutoFilter 1, .Value

            End If
        End If
    End With
    Application.EnableEvents = True    'Turn flag back On
End Sub

How do i filter a range in another sheet with VBA without activating the sheet

Additional Information (Edit):

1 Answers