Using VBA to autofilter Multiple columns, with values from different sheet

Question

I want to use VBA to filter a dump-sheet by 2 columns, with criteria gotten from values on a different sheet in the same workbook.

Used code is:

Sub FilterOnCellValue()

With Sheets("Dump")
    .Range("A1:Z10000").AutoFilter Field:=9, Criteria1:=Sheets("ControlPlanning").Range("C1").Value, Field:=23, Criteria1:=Sheets("ControlPlanning").Range("C4").Value
End With

End Sub

For some reason this code filters only one column, while it should be filtering Columns with Number 9 and 23 on 2 different values. As i want to learn from this, explain my thinking error in this piece of VBA. Excel version is 2013, if this makes any difference.

Solution

Try to seperate the syntax to 2 lines:

Sub FilterOnCellValue()

With Sheets("Dump").Range("A1:Z10000")
    .AutoFilter Field:=9, Criteria1:=Sheets("ControlPlanning").Range("C1").Value
    .AutoFilter Field:=23, Criteria1:=Sheets("ControlPlanning").Range("C4").Value
End With

End Sub

Comments

[Pᴇʜ]

Possible duplicate of Multiple Filtering in Excel VBA?

[Siyon DP]

According to Range.AutoFilter MSDN you cannot twice Field parametter..

[RobExcel]

@Peh, no criteria from value there.

[RobExcel]

@TSion.D.P, works like a dime, whatever MSDN is saying.

[Pᴇʜ]

@RobExcel It doesn't matter if you use a criteria from a cell value or a fixed string. The syntax around is the same, as you can see in the answer below.

[RobExcel]

@Peh Fair enough. Got the answer I needed, so, what's the deal?

[Pᴇʜ]

@RobExcel The deal is you stated in your question you "want to learn from this". So my aim was showing you that there is already a similar (almost same) question which already has the answer you were looking for (with only little knowledge transfer). So learning (how) to use search and find similar/same questions already asked and transfer that knowledge into your use case would be a much grater benefit for you then getting the solution on the silver plate. That was the deal.