Copy row from one sheet to another

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

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: