I want the row I copy into new sheet to stay in original sheet as well (script)

[runeroan]

Hi, this script copy the entire row into another sheet based on text value in a cell. Copy works fine.
However it also delete the row copied from original sheet, and I need it to stay! I need the row to stay and be copied, not copied and deleted. How can I get rid of line "Cell.EntireRow.Delete" as this does the delete? If I remove the line I get error. Please help. Thanks.


Sub Kopiere_til_I_produksjon()
Lastrow = Worksheets("Hertz").UsedRange.Rows.Count
lastrow2 = Worksheets("I_produksjon").UsedRange.Rows.Count
If lastrow2 = 1 Then
lastrow2 = 0
Else
End If
Do While Application.WorksheetFunction.CountIf(Range("D:D"), "Innlevert") > 0
Set Check = Range("D1:D" & Lastrow)
For Each Cell In Check
If Cell = "Innlevert" Then
Cell.EntireRow.Copy Destination:=Worksheets("I_produksjon").Range("A" & lastrow2 + 1)
Cell.EntireRow.Delete
lastrow2 = lastrow2 + 1
Else:
End If
Next
Loop
End Sub

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

Worked fine, thank you for great knowledge and help!

 

[runeroan]

Code worked fine. However, after entire row is copied into sheet "I_produksjon" this is next step in process:

Text "Innlevert" will later change to "Levert" in sheet "I_produksjon". This is what should happen when text "Innlevert" change to "Levert" in sheet "I_produksjon":

Text should also change in sheet "Hertz" from Innlevert to "Levert" and entire row in sheet "I_produksjon" should be deleted.
Green background that was made in sheet "Hertz" should be removed (no fill).

Possible?

 

[My Aswer Is This]

I provided you with a script and you said "Worked fine"

Now your saying:
Code worked fine. However, after entire row is copied into sheet "I_produksjon" this is next step in process: ...

I do not need you giving me one step at a time as to what you want.

You should say from the beginning all of what you want.

And when you say "Next step" sounds like you may have 2 or 3 other steps you plan to tell me about later.

 

[runeroan]

Hi, this is the last step in process for sure.
I found that it's a bit cumbersome to manually delete the row and change the other sheet.
If possible, could you help?
Rune

 

[My Aswer Is This]

So now your saying if I understand:

You want the same exact row to be copied backed to the same exact row in the original sheet when the value "Levert" is entered into the cell on sheet named "I_produksjon"

How am I going to know what exact row to copy this row into?

 

[runeroan]

If there are a coloumn with a specific number in it, will it then be possible to copy back the entire row from I_produksjon and overwrite the old row in Hertz sheet?

 

[My Aswer Is This]

Unless there is some unique value in some column
Like account number.

Or we could have the script put row number in say column 45 like current row number when first copied over
Then when we want to copy back over we look in column 45 for the row number and copy back to same row.

Would that work. We would need to put the row number into a column where you have no other data. And if needed we then clear the row number in column 45

 

[runeroan]

That could work, nice idea to put the row number in an unseen column like 45 as suggested. Then we hane an unique number to use when copying entire row from I_produksjon back to Hertz with Levert in column D. Can you make it happen?

 

[My Aswer Is This]

I will work on it. Check back later in the day for a two new scripts.

That could work, nice idea to put the row number in an unseen column like 45 as suggested. Then we hane an unique number to use when copying entire row from I_produksjon back to Hertz with Levert in column D. Can you make it happen?

 

[runeroan]

Looking forward to, thank you for helping me solve this challenge.