Changing value of cell after copy is complete.

[Ryan_s]

Hey everyone, first post and excel newbie. I created some VBA code to copy certain cells of a row to a different worksheet based on criteria. I'm trying to add to the code so that "Log Assignment" is changed to "Assigned" on the source worksheet so that that row is no longer is copied when the code is executed again.

This is the code I'm using. I tried adding different things at the end but was not able to change the criteria cell after the copy. Thanks

Private Sub CommandButton1_Click()

Dim lastrow As Long, erow As Long

lastrow = Worksheets("Options Sell").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lastrow


If Worksheets("Options Sell").Cells(i, 17).Value = "Log Assignment" Then

Worksheets("Options Sell").Cells(i, 1).Copy

erow = Worksheets("Stocks").Cells(Rows.Count, 1).End(xlUp).Row

Worksheets("Options Sell").Paste Destination:=Worksheets("Stocks").Cells(erow + 1, 1)

Worksheets("Options Sell").Cells(i, 5).Copy

Worksheets("Options Sell").Paste Destination:=Worksheets("Stocks").Cells(erow + 1, 3)

Worksheets("Options Sell").Cells(i, 6).Copy

Worksheets("Options Sell").Paste Destination:=Worksheets("Stocks").Cells(erow + 1, 2)

Worksheets("Options Sell").Cells(i, 17).Copy

Worksheets("Options Sell").Paste Destination:=Worksheets("Stocks").Cells(erow + 1, 12)


End If

Next i

End Sub

 

[Ryan_s]

One other thing, I noticed when I execute the copy command the criteria cell has a moving green dotted border around it. Is there a way so it doesn't have this border?

 

[Logit]

Untested here :

Private Sub CommandButton1_Click()

Dim lastrow As Long, erow As Long

lastrow = Worksheets("Options Sell").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lastrow


    If Worksheets("Options Sell").Cells(i, 17).Value = "Log Assignment" Then
    
        Worksheets("Options Sell").Cells(i, 1).Copy
        
        erow = Worksheets("Stocks").Cells(Rows.Count, 1).End(xlUp).Row
        
        Worksheets("Options Sell").Paste Destination:=Worksheets("Stocks").Cells(erow + 1, 1)
        
        Worksheets("Options Sell").Cells(i, 1).Value = "Assigned"
        
        Worksheets("Options Sell").Cells(i, 5).Copy
        
        Worksheets("Options Sell").Paste Destination:=Worksheets("Stocks").Cells(erow + 1, 3)
        
        Worksheets("Options Sell").Cells(i, 6).Copy
        
        Worksheets("Options Sell").Paste Destination:=Worksheets("Stocks").Cells(erow + 1, 2)
        
        Worksheets("Options Sell").Cells(i, 17).Copy
        
        Worksheets("Options Sell").Paste Destination:=Worksheets("Stocks").Cells(erow + 1, 12)
    
    
    End If

Next i

End Sub

 

[Ryan_s]

Untested here :

Private Sub CommandButton1_Click()

Dim lastrow As Long, erow As Long

lastrow = Worksheets("Options Sell").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lastrow


    If Worksheets("Options Sell").Cells(i, 17).Value = "Log Assignment" Then
   
        Worksheets("Options Sell").Cells(i, 1).Copy
       
        erow = Worksheets("Stocks").Cells(Rows.Count, 1).End(xlUp).Row
       
        Worksheets("Options Sell").Paste Destination:=Worksheets("Stocks").Cells(erow + 1, 1)
       
        Worksheets("Options Sell").Cells(i, 1).Value = "Assigned"
       
        Worksheets("Options Sell").Cells(i, 5).Copy
       
        Worksheets("Options Sell").Paste Destination:=Worksheets("Stocks").Cells(erow + 1, 3)
       
        Worksheets("Options Sell").Cells(i, 6).Copy
       
        Worksheets("Options Sell").Paste Destination:=Worksheets("Stocks").Cells(erow + 1, 2)
       
        Worksheets("Options Sell").Cells(i, 17).Copy
       
        Worksheets("Options Sell").Paste Destination:=Worksheets("Stocks").Cells(erow + 1, 12)
   
   
    End If

Next i

End Sub

Thanks, almost worked. It didn't change the source cell (Log Assignment) but did change the first column in the "Stocks" worksheet to assigned.

 

[Logit]

Change:

Worksheets("Options Sell").Cells(i, 1).Value = "Assigned"

To:

Worksheets("Options Sell").Cells(i, 17).Value = "Assigned"

 

[Ryan_s]

Thank you, that worked! Any thoughts on removing the green moving border?

 

[Logit]

Try pasting the following just above the "End Sub" line :

Application.CutCopyMode = False

 

[Ryan_s]

Try pasting the following just above the "End Sub" line :

Application.CutCopyMode = False

Perfect, that worked. Thank you so much!

 

[Logit]

You are welcome.