VBA copy sheet 1 to Array and put relevant information on sheet 2

[Peteor]

Hello! I have a worksheet where Columns A:Z are used. I would like to take all rows on sheet 1 where Column E has a value of "2" AND Column J has a value of "Example", and copy the information from the Entire Row to sheet 2. Row 1 on sheet 1 is just header information. I would also like this on sheet 2.

I recognize this to be a very broad question, and believe this to be done with "CreateObject("scripting.dictionary")"... I would like to understand how to use this class on my own, but am struggling. Any comments would be greatly appreciated...

 

[Fluff]

The simplest way of doing that would be with an autofilter

Sub Peteor()
   
   With Sheets("Sheet1")
      .Range("A1:Z1").AutoFilter 5, 2
      .Range("A1:Z1").AutoFilter 10, "Example"
      .AutoFilter.Range.Copy Sheets("sheet2").Range("A1")
      .AutoFilterMode = False
   End With
End Sub

 

[Peteor]

I over-complicate things. The provided macro functions exactly as intended. Thank you.

 

[Fluff]

You're welcome & thanks for the feedback

 

[Peteor]

The following does not work, but I would like it to:

.Range("A1:Z1000").AutoFilter Field:=5, Criteria1:="3", Operator:=xlAnd, Criteria2:="4", Operator:=xlAnd, Criteria3:="5"
.Range("A1:Z1000").AutoFilter 10, "Example"

I would like to filter off all possibilities (Integers 1 - 7) except Criteria 1-3 listed above, and then execute the next line.

What would you advise here?

 

[Fluff]

Try

      .Range("A1:Z1").AutoFilter 5, Array(3, 4, 5), xlFilterValues

 

[Peteor]

I ran it and it did not work. I stepped through it, and when it ran the line you provided, none of the filter criteria were checked. The issue is not an error, so the .AutoFilter.Range.Copy (next line) copys a blank sheet.

 

[Fluff]

It should be

      .Range("A1:Z1").AutoFilter 19, Array("3", "4", "5"), xlFilterValues

Keep forgetting to put them as strings

 

[Peteor]

That's the one. Thank you!

 

[Fluff]

My pleasure