Only the top row is being copied

[ExcelPupper]

I have here my code that does not run properly because when I press F5 it only copies the data from the top row, but when I do the step into (F8) it works fine. Is there something I should modify on the code below?

Sub CopyData()
'
'
' Keyboard Shortcut: Ctrl+Shift+Q
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
   
    Sheets(1).Activate
    ActiveSheet.Range("A1:D" & LR).AutoFilter Field:=1, Criteria1:="High"
    ActiveSheet.Range("A1:D" & LR).AutoFilter Field:=2, Criteria1:="1000"
    ActiveSheet.Range("A1:D" & LR).AutoFilter Field:=3, Criteria1:="10"
    Range("A1:D" & LR).Select
    Selection.Copy
    Sheets("Class1").Select
    ActiveSheet.Paste
    Sheets(1).Select
    ActiveSheet.Range("$A$1:D" & LR).AutoFilter Field:=3, Criteria1:="11"
    Range("A1:D" & LR).Select
    Selection.Copy
    Sheets("Class2").Select
    ActiveSheet.Paste

End Sub

 

[MARK858]

Try putting the LR = line below the Sheets(1).Activate line

 

[ExcelPupper]

Try putting the LR = line below the Sheets(1).Activate line

Ohh great! Thanks for this.

 

[MARK858]

You're welcome. When I get back in I'll post some code to do away with those Selects and Activates to make the code a bit more efficient (a bit of a pain to code on a phone).

 

[ExcelPupper]

You're welcome. When I get back in I'll post some code to do away with those Selects and Activates to make the code a bit more efficient (a bit of a pain to code on a phone).

That's nice, thanks. Btw, Im learning to code thru macro recording (just a newbie) and I'm just experimenting on how to prepare my reports easily ?

 

[MARK858]

Try the code below, please note that you might have to reset the keyboard shortcut.
Btw, using the recorder isn't the worst way of learning basic VBA syntax

Sub CopyData2()
    '
    '
    ' Keyboard Shortcut: Ctrl+Shift+Q
    Dim LR As Long
    Application.ScreenUpdating = False
 
    With Sheets(1)
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        With .Range("A1:D" & LR)
            .AutoFilter 1, "High"
            .AutoFilter 2, "1000"
            .AutoFilter 3, "10"
            .Copy Sheets("Class1").Cells(1, "A")
            .AutoFilter 3, "11"
            .Copy Sheets("Class2").Cells(1, "A")
        End With
    End With
   
    Application.ScreenUpdating = True
End Sub

 

[ExcelPupper]

Try the code below, please note that you might have to reset the keyboard shortcut.
Btw, using the recorder isn't the worst way of learning basic VBA syntax

Sub CopyData2()
    '
    '
    ' Keyboard Shortcut: Ctrl+Shift+Q
    Dim LR As Long
    Application.ScreenUpdating = False

    With Sheets(1)
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        With .Range("A1:D" & LR)
            .AutoFilter 1, "High"
            .AutoFilter 2, "1000"
            .AutoFilter 3, "10"
            .Copy Sheets("Class1").Cells(1, "A")
            .AutoFilter 3, "11"
            .Copy Sheets("Class2").Cells(1, "A")
        End With
    End With
  
    Application.ScreenUpdating = True
End Sub

Thanks for this. However I have a follow up question, on this line

 .AutoFilter 2, "1000"

I tried to add another criteria which looks like this

 .AutoFilter 2, "1000", "1500"

and it results to run time error. Is there a proper way to add criteria and is it only limited up to 2 only?

 

[MARK858]

Try

.AutoFilter 2, "1000", xlOr, "100"

and yes you are restricted to 2 criteria when using a regular autofilter. You would either use an Array or Advanced filter if you needed more criteria.

 

[ExcelPupper]

Try

.AutoFilter 2, "1000", xlOr, "100"

and yes you are restricted to 2 criteria when using a regular autofilter. You would either use an Array or Advanced filter if you needed more criteria.

What if I use the "does not contain criteria"?
This is what I tried and it gives error

.AutoFilter 2, "<>1000", xlOr, "<>1500"

 

[MARK858]

.AutoFilter 2, "<>1000", xlAnd, "<>100"