Copy paste to another sheet only once when the condition is met

[ecelebi16]

Hello,

I am trying to improve a tracking a sheet that we go in once a while and update the conditions. If the condition is 1 (meaning 100) then we want to paste into the "Wins" sheet for the selected ranges. The code I have works to paste the entire data when the condition is met, but when I click on the button to update, then it updates everything all over again. I would like to have it updated only once and the next available empty row on "Wins" sheet.

I tried two things:
1) this one runs and copies all the data

Private Sub CommandButton1_Click()

a = Worksheets("IC Pipeline").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To a

  If Worksheets("IC Pipeline").Cells(i, 11).Value = "1" Then
     Worksheets("IC Pipeline").Rows(i).Copy
     Worksheets("Wins").Activate
     b = Worksheets("Wins").Cells(Rows.Count, 1).End(xlUp).Row
     Worksheets("Wins").Cells(b + 1, 1).Select
     ActiveSheet.Paste
          Worksheets("IC Pipeline").Activate
     End If
     
     Next
     
     Application.CutCopyMode = False
     
     ThisWorkbook.Worksheets("IC Pipeline").Cells(1, 1).Select
     
End Sub

2) This one gives me an error "Wrong number of arguments or invalid property assignment"

Private Sub CommandButton2_Click()

Dim LastRow As Integer, i As Integer, erow As Integer

LastRow = Worksheets("IC Pipeline").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To LastRow

    If Worksheets("IC Pipeline").Cells(i, 11).Value = "1" Then
    Range(Cells(i, 1), Cells(i, 2), Cells(i, 3), Cells(i, 4), Cells(i, 7), Cells(i, 8), Cells(i, 9), Cells(i, 10), Cells(i, 11), Cells(i, 12), Cells(i, 13), Cells(i, 14)).Select
    Selection.Copy
    Worksheets("Wins2").Activate
    erow = Worksheets("Wins2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    Worksheets("Wins2").Cells(erow, 1).Select
    ActiveSheet.Paste
    ThisWorkbook.Save
    Application.CutCopyMode = False
    End If

Next i

End Sub

*********

Can someone help me figure this out ??

Thank you!
ecelebi16

[bulina2k]

What do you want to copy to "WINS", only the last record in "IC Pipeline" or whatever is selected when you click on the button?

[PaulM100]

Try this:

Private Sub CommandButton1_Click()

Dim a As Long
Dim b As Long
Dim wbS As Worksheet
Dim wbD As Worksheet

Set wbS = ThisWorkbook.Worksheets("IC Pipeline")
Set wsd = ThisWorkbook.Worksheets("Wins")

a = wbS.Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To a

If wbS.Cells(i, 11).Value = "1" Then
wbS.Rows(i).Copy
wsd.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If

Next
Worksheets("IC Pipeline").Activate
End Sub

[ecelebi16]

Hi bulinak, I have added the attachment. So you see that column D, the probability gets updated when it is a win, meaning 100. So when I hit update it should add the data at the end of the wins tab when there is potential changes. This change is random, but I want that updated data to be at the end of the wins file when there is change.

But coding updates everything every time when I hit update.

Thank you!

[Sintek]

What about forum Rules here
2. Programming code must be enclosed in code tags to improve readability. (A, Z)

 Your Code

(or use the # button)

'! No looping required...Use AutoFilter

[ecelebi16]

Hi PaulM100 , thanks for your response. I used your coding and it does the same thing.. When I go back for the second time, it updates the entire list again.. I only need to have it update it once the probability turns to 1. I attached my excel if you want to take a look at it.

Thank you!

[6StringJazzer]

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #2 requires code tags. I have added them for you this time because you are a new member. I have also noted that you ignored sintek's post about this. In the future that can earn warnings. --6StringJazzer

[PaulM100]

Then you don't need a button, you need it to trigger automatically on cell change. Right click on IC pipeline tab - view code and insert the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wsCopy As Worksheet
    Dim rngCopy As Range
    Dim wsPaste As Worksheet
    Dim rngPaste As Range
    
    If Target.Column = 4 Then
    
        If Target.Value = "1" Then
            Set wsCopy = ActiveSheet
            Set wsPaste = ActiveWorkbook.Worksheets("Wins")
            Set rngCopy = wsCopy.Range("A" & Target.Row & ":U" & Target.Row)
            rngCopy.Select
            Set rngPaste = wsPaste.Range("A" & wsPaste.Range("A" & Rows.Count).End(xlUp).Row + 1)
            
            rngCopy.Copy
            rngPaste.PasteSpecial
            Application.CutCopyMode = False
            Set rngCopy = Nothing
            Set rngPaste = Nothing
            Set wsCopy = Nothing
            Set wsPaste = Nothing
        End If
    End If
End Sub