Copy And Paste Depening On Criteria

[blood_Milk_sky]

Hey,

I was just wondering if anyone could help me with the below. I am trying to only Copy and Paste the rows that exceed "0", I have tried running this code but every time I do I get a run time error. could anyone help me please? an any improvements or suggestions would be greatly appreciated

many thanks

BMS

Sub ADDTOORDER()

    Sheets("Menu").Select
    Range("B7:D68").AutoFilter Field:=3, Criteria1:=">0", Operator:=xlAnd
    Range("B7:D68").Copy
    Sheets("LensOrder").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
    Sheets("Menu").Range("C3").Select
    Selection.ClearContents
    Range("QTYS").Select
    Selection.ClearContents
    Range("C3").Select
    
End Sub

 

[belkacem]

Try this

Sub ADDTOORDER()
Dim Sh As Worksheet, C As Worksheet, Last As Long
Set Sh = Sheets("Menu")
Set C = Sheets("LensOrder")
With Sh
Last = .Cells(Rows.Count, 2).End(xlUp).Row
    .Range("B7:D" & Last).AutoFilter Field:=2, Criteria1:=">0", Operator:=xlAnd
    .Range("B7:D" & Last).SpecialCells(xlCellTypeVisible).Copy
    C.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
    Sheets("Menu").Range("C3").Select
   .Range("B7:D" & Last).AutoFilter
End With
End Sub

 

[blood_Milk_sky]

Thank you for your response. I have tried the Code and it only Copys an paste the first row of the range B7:d68 to the sheet "LensOrder"

 

[belkacem]

Hello
I hope that you verify this part :
Especially number 2


Field:=2, Criteria1:=">0"

 

[blood_Milk_sky]

sorry I am still learning how to write VB, How do you mean verify?