title: How do i filter a range in another sheet with VBA without activating the sheet
body: <p>Good day,</p>
  
  <p>I am having problems with Set Ranges and it has been quite frustrating when using set ranges from non-active sheets. </p>
  
  <p>The problem is:</p>
  
  <p>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.</p>
  
  <p>The code below is the one i'm using to filter the range. It is inserted in the "Dashboard" Sheet object.</p>
  
  <pre><code>Private Sub DashboardBudgetlst_Change()
  
  Dim rng As Range
  Dim i As Integer
  
    i = Me.DashboardBudgetlst.ListIndex
  
      If i &gt;= 0 Then
       If Me.DashboardBudgetlst.Selected(i) And Me.DashboardBudgetlst.Column(0, i) &lt;&gt; "" Then
            Set rng = Budget.Range("B1:E" &amp; lrow(Budget, "A"))
            rng.AutoFilter 1, Me.DashboardBudgetlst.Column(1, i)
            Set rng = Nothing
       End If
     End If
  
  End Sub
  </code></pre>
  
  <p>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.</p>
  
  <p>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).</p>
  
  <p>Would you guys know the workaround and why there is this problem with Set Ranges?</p>
  
  <p>I know there are similar questions about ranges, but none with the same specifications.</p>
  
  <h2>Additional Information (Edit):</h2>
  
  <p><strong>1-</strong> Error is on line:</p>
  
  <pre><code> rng.AutoFilter 1, Me.DashboardBudgetlst.Column(1, i)
  </code></pre>
  
  <p><strong>2-</strong> Listbox index >= 0 to ensure that listbox is not empty and there's an item selected. When a listbox is empty the listindex = -1. </p>
  
  <p><strong>3-</strong> lrow(Budget, "A") calls the following function to get the last row in the specified sheet:</p>
  
  <pre><code>   Function lrow(SH As Worksheet, col As String)
          lrow = SH.Cells(Rows.Count, col).End(xlUp).Row
     End Function
  </code></pre>
  
  <p><strong>4-</strong> With msgbox rng.address just before the error line, i receive $B$1:$E$5 as the address.</p>
  
  <p><strong>5-</strong> I did a temporary workaround using</p>
  
  <pre><code>On Error Resume Next
  </code></pre>
  
  <p><strong>6-</strong> 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.</p>
  
  <p><strong>7-</strong> Debug.Print on :</p>
  
  <pre><code>Debug.Print rng.AutoFilter; 1, Me.DashboardBudgetlst.Column(1, i)
  </code></pre>
  
  <p>Selected on Travel Expenses in listbox Returns on Immediate Window:</p>
  
  <pre><code>True 1        Travel Expenses
  </code></pre>
  
  <p><strong>8-</strong> Some Screenshots:</p>
  
  <p>Listbox in Dashboard Sheet (Excel View)
  <a href="https://i.stack.imgur.com/bXr9m.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/bXr9m.jpg" alt="Listbox in Dashboard Sheet (Excel View)"></a></p>
  
  <p>Range in Budget Sheet (Excel View)
  <a href="https://i.stack.imgur.com/5tzy2.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/5tzy2.jpg" alt="Range in Budget Sheet (Excel View"></a></p>
  
  <p>Objects being used (VBA view)
  <a href="https://i.stack.imgur.com/V7iAz.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/V7iAz.jpg" alt="Objects being used (VBA view)"></a></p>
  
  <p>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?)</p>