Filter for selected options and create a new sheet VBA Macro Reader

[beginvbaanalyst]

Hi Everyone,

I created a macro using the Macro Recorder but wanted to see if there's a much easier way to filter and paste a new sheet with less code.
I want it to filter for anything within the column and not a selected range ("$I$1:$M$83").
Would I use something like to select anything within the column:
Range("I2", Range ("I" & Rows.Count).End(xlUp)

Essiently I want to end up look up anything that has a 0 within my J Column.

My Macro Recorder Code is:
Sub TestFilter()
'
' TestFilter Macro
' EinaFilter
'

'
Range("I1:M1").Select
Selection.AutoFilter
ActiveSheet.Range("$I$1:$M$83").AutoFilter Field:=2, Criteria1:="1"
ActiveSheet.Range("$I$1:$M$83").AutoFilter Field:=3, Criteria1:="0"
Sheets("Sheet1").Select
Sheets("Sheet1").Copy After:=Sheets(1)
End Sub

 

[DanteAmor]

Try this:

Sub TestFilter()
  If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
  With Range("I1:M" & Range("J" & Rows.Count).End(xlUp))
    .AutoFilter Field:=2, Criteria1:="1"
    .AutoFilter Field:=3, Criteria1:="0"
  End With
  Sheets("Sheet1").Copy After:=Sheets(1)
End Sub

If you only want to paste the filtered data in a new sheet, then:

Sub TestFilter()
  If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
  With Range("I1:M" & Range("J" & Rows.Count).End(xlUp))
    .AutoFilter Field:=2, Criteria1:="1"
    .AutoFilter Field:=3, Criteria1:="0"
    ActiveSheet.AutoFilter.Range.EntireRow.Copy
    Sheets.Add after:=Sheets(1)
    ActiveSheet.Paste
  End With
End Sub

 

[beginvbaanalyst]

Try this:

Sub TestFilter()
  If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
  With Range("I1:M" & Range("J" & Rows.Count).End(xlUp))
    .AutoFilter Field:=2, Criteria1:="1"
    .AutoFilter Field:=3, Criteria1:="0"
  End With
  Sheets("Sheet1").Copy After:=Sheets(1)
End Sub

If you only want to paste the filtered data in a new sheet, then:

Sub TestFilter()
  If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
  With Range("I1:M" & Range("J" & Rows.Count).End(xlUp))
    .AutoFilter Field:=2, Criteria1:="1"
    .AutoFilter Field:=3, Criteria1:="0"
    ActiveSheet.AutoFilter.Range.EntireRow.Copy
    Sheets.Add after:=Sheets(1)
    ActiveSheet.Paste
  End With
End Sub

Thank you for the response, I keep getting a compile error: Named argument not found
Is that because the second line of Criteria1 doesn't have any zeros?
I tried replacing with
.AutoFilter Field:=3, Criteria1:="1"
I know that in this column all fields are 1 and it keeps displaying the "named argument not found".
Can't wrap my head around why

 

[DanteAmor]

Did you modify something in the code?
Did you copy it full?
If there is no data, it simply does not filter anything, but does not send an error.

Thank you for the response, I keep getting a compile error: Named argument not found

On which line does the macro stop?

 

[beginvbaanalyst]

Did you modify something in the code?
Did you copy it full?
If there is no data, it simply does not filter anything, but does not send an error.



On which line does the macro stop?

I was able to get it to work.
My problem is the If Active.Sheet.AutoFilterMode then ActiveSheete.AutoFilter Mode = False
This wouldn't let me run the code at all and I had to remove it.
Should I set this up as a new breakpoint by itself after I create a new sheet with the copied filter data?

 

[DanteAmor]

If Active.Sheet.AutoFilterMode then ActiveSheete.AutoFilter Mode = False

It is not written correctly, it should be like this:

If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False

Just copy the line and paste in your macro.

 

[beginvbaanalyst]

It is not written correctly, it should be like this:

If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False

Just copy the line and paste in your macro.

Thank you so much! I appreciate all your help!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.