title: ComboBox to filter data in another sheet
body: <p>I am trying to make a ComboBox dropdown with some criteria inside.<br />
  <a href="https://i.stack.imgur.com/NwLBX.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/NwLBX.jpg" alt="enter image description here" /></a></p>
  <p>I want to Filter by Total Product Holding.</p>
  <p>This ComboBox is in &quot;Main Menu&quot; sheet, and the data to be filtered is in &quot;Consolidated Database&quot; sheet.<br />
  <a href="https://i.stack.imgur.com/2wqAE.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/2wqAE.jpg" alt="enter image description here" /></a></p>
  <p>So, when I select the criteria from the dropdown list, the data in the &quot;Consolidated Database&quot; is filtered based on that selection.</p>
  <p>For example, I select &quot;1&quot; from the dropdown list, then the data in the &quot;Consolidated Database&quot; is filtered by &quot;1&quot; on Column BC (&quot;SUM PH ALL&quot;), and all the data in that worksheet also filtered by that criteria.<br />
  What should happen if I select &quot;1&quot; in the dropdown list in the &quot;Main Menu&quot; sheet:<br />
  <a href="https://i.stack.imgur.com/uKEgo.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/uKEgo.jpg" alt="enter image description here" /></a></p>
  <p>The VBA code says error.</p>
  <pre><code>Private Sub ComboBox1_Change()
      Dim lastr As Integer
      lastr = Range(&quot;BC&quot; &amp; Rows.Count).End(xlUp).Row
      Sheets(&quot;Consolidated Database&quot;).Range(&quot;BC2:BC&quot; &amp; lastr).AutoFilter _
      Field:=55, Criteria1:=ComboBox1.Value, Operator:=x1FilterValues
  End Sub
  
  Private Sub ComboBox1_DropButtonClick()
      Me.ComboBox1.ListFillRange = &quot;SUM_PH_ALL&quot;
  End Sub
  </code></pre>