Re: Copy row from one sheet to another
Glad you could sort out the various issues of your project ...:wink:
Thanks a lot for your kind words ...:smile:
Re: Copy row from one sheet to another
Glad you could sort out the various issues of your project ...:wink:
Thanks a lot for your kind words ...:smile:
This code does not work as intended. Once it deleted a row it looks to the next row to carry on with the code. BUT by deleting a row the next row has moved up one. So things can be missed when two rows meet the criteria one after the other.
Its worth mentioning it on an old thread becausae this ranks quite highly in google for this type of search.
Andy.
Hello,
Would you mind attaching a small file ... for test purposes ... :wink:
Please see attached. I have tried adding a goto event before the loop so when one is detected and deleted it would start again. No luck yet but thats the only idea I have. I'm out the office now till tomorrow but any questions or feedback let me know.
Thanks. Jobs Tracking Test.xlsm
for information: If you put Yes in column "I" on multiple rows one after the other. It will miss any consecutive rows. This is because it will be moved into the deleted rows space and not checked.
Sub ArchiveCompleted()
'
' ArchiveCompleted Macro
Dim lastrs As Long
Dim lastrd As Long
Dim wks As Worksheet
Dim wkd As Worksheet
Dim swk As String
Dim c As Range
Dim rng As Range
Set wks = ActiveSheet
swk = wks.Name
Set wkd = Worksheets("Completed Orders")
lastrs = ActiveSheet.Cells(Application.Rows.Count, "B").End(xlUp).Row
lastrd = wkd.Cells(Application.Rows.Count, "A").End(xlUp).Row + 1
Set rng = wks.Range("I2:I" & lastrs)
Start:
For Each c In rng
If c = "Yes" Then
' Copy Data to Archived
wkd.Range("A" & lastrd & ":" & "J" & lastrd).Value = wks.Range("A" & c.Row & ":" & "J" & c.Row).Value
' Add Source Name in Column G. Possible to seegregate projects using this method and still track payments
'wkd.Range("G" & lastrd) = swk
' Delete Entire Row in Source sheet
wks.Rows(c.Row & ":" & c.Row).Delete
GoTo Start
End If
Next c
End Sub
Display More
Sorry, This will work, we do not need to check for if there is yes after deletion. we can just go to start. New problem: it will only copy one row over with this code. All others disappear.
Just to make sure my understanding is correct ...
Do you need to have your record immediately ( and automatically...) archived as soon as you input Yes in Column I ...
or would you rather have a batch process launched with a button ...?
Without your feedback ...
Attached is your Test file with the two Solutions ...
Hope this will help
You are a legend. Thanks.
Glad you could fix your problem ... :wink:
Thanks for your very kind words ...AND for the Like ...:smile:
Don’t have an account yet? Register yourself now and be a part of our community!