Don't want to Copy Data Twice

[nabeelahmed]

Hi everyone, I am copying data from one sheet to other using PRNo criteria, whenever i enter PRNo. in cell "from" and "To" and how many time i press my Macro Button it is being copy every time to the next row, i don't want same PRno. data to be copied more than one time. It should be copy once and for the next entry when i will use another PRno. then next data should be copy in the next rows and so on..... my coding is below .

Private Sub CommandButton1_Click()

Dim fromPRNo As String: fromPRNo = Sheet2.[E3].Value
Dim ToPRNo As String: ToPRNo = Sheet2.[F3].Value


Application.ScreenUpdating = False


With Sheet1.[A1].CurrentRegion
.AutoFilter 3, ">=" & fromPRNo, xlAnd, "<=" & ToPRNo

.Offset(1).EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)



.AutoFilter
End With

Sheet2.Columns.AutoFit

Application.ScreenUpdating = True

End Sub

 

[davesexcel]

Maybe send those values to a different cell, then check if they don't equal before continuing.....

Dim fromPRNo As String: fromPRNo = Sheet2.[E3].Value

Dim ToPRNo  As String: ToPRNo = Sheet2.[F3].Value

Application.ScreenUpdating = FALSE

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

If Sheet2.[Z3].Value<>fromPRNo And Sheet2.[AA3].Value<>ToPRNo Then
    
    '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    
    With Sheet1.[A1].CurrentRegion
        
        .AutoFilter 3, ">=" & fromPRNo, xlAnd, "<=" & ToPRNo
        
        .Offset(1).EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
        
        .AutoFilter
        
    End With
    
    Sheet2.Columns.AutoFit
    
    '<<<<<<<<<<<<<<<<
    
    Sheet2.[Z3].Value=fromPRNo
    
    Sheet2.[AA3].Value=ToPRNo
    
    '>>>>>>>>>>>>>>>>>
    
    Application.ScreenUpdating = TRUE
    
    '<<<<<<<<<<<<<
    
End If

'>>>>>>>>>>>>>>>>

End Sub

 

[nabeelahmed]

Maybe send those values to a different cell, then check if they don't equal before continuing.....

Dim fromPRNo As String: fromPRNo = Sheet2.[E3].Value

Dim ToPRNo  As String: ToPRNo = Sheet2.[F3].Value

Application.ScreenUpdating = FALSE

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

If Sheet2.[Z3].Value<>fromPRNo And Sheet2.[AA3].Value<>ToPRNo Then
   
    '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
   
    With Sheet1.[A1].CurrentRegion
       
        .AutoFilter 3, ">=" & fromPRNo, xlAnd, "<=" & ToPRNo
       
        .Offset(1).EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
       
        .AutoFilter
       
    End With
   
    Sheet2.Columns.AutoFit
   
    '<<<<<<<<<<<<<<<<
   
    Sheet2.[Z3].Value=fromPRNo
   
    Sheet2.[AA3].Value=ToPRNo
   
    '>>>>>>>>>>>>>>>>>
   
    Application.ScreenUpdating = TRUE
   
    '<<<<<<<<<<<<<
   
End If

'>>>>>>>>>>>>>>>>

End Sub

Dear friend, Thanks for your help but it is still copying the same PRno data which is already copied, It should not copy the same data again in that sheet..