Macro to transfer Row Data from one existing sheet to another existing sheet based on specific cell text in column P

[roharriso]

I have an excel file consisting of two tabs, "Active" and "Historical". Both sheets are formatted with same header information that runs from cells A11 to O11. The "Active" sheet is updated continuously throughout the day, running as far down as row 300 in sheet. At end of shift, I want my associate to use a macro to pull rows within the field once column P reflects the text "COMPLETE", leaving all the other rows with blanks in column P for continued use. I have conditional formatting applied to "Active" sheet so that rows that have "COMPLETE" in column "O" turn yellow. On the "Historical" tab, I would like the new rows to be added beneath any previously existing data.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[TD]O
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]SHIFT
[/TD]
[TD]DOCK DOOR
[/TD]
[TD]DATE RCV'D
[/TD]
[TD]VENDOR
[/TD]
[TD]TRL #
[/TD]
[TD]CASES
[/TD]
[TD]PLTS
[/TD]
[TD]UNLOADED START
[/TD]
[TD]UNLOAD FINISHED
[/TD]
[TD]ASN #
[/TD]
[TD]UNLOADER
[/TD]
[TD]Load Type
[/TD]
[TD]PO
[/TD]
[TD]SCANNER
[/TD]
[TD]STATUS
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]2nd
[/TD]
[TD]119
[/TD]
[TD]11/18
[/TD]
[TD]Central Aquatics
[/TD]
[TD]245
[/TD]
[TD]4522
[/TD]
[TD]34
[/TD]
[TD]1730
[/TD]
[TD]1925
[/TD]
[TD]2439
[/TD]
[TD]Artimus
[/TD]
[TD]C
[/TD]
[TD]123345
[/TD]
[TD]SIMS
[/TD]
[TD]COMPLETE
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]2nd
[/TD]
[TD]120
[/TD]
[TD]11/18
[/TD]
[TD]Shandong
[/TD]
[TD]58R5
[/TD]
[TD]3245
[/TD]
[TD]52
[/TD]
[TD]1752
[/TD]
[TD]2230
[/TD]
[TD]2372
[/TD]
[TD]Delta
[/TD]
[TD]L
[/TD]
[TD]3456836
[/TD]
[TD]SMITH
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[vcoolio]

Hello Roharriso,

Try the following code placed in a standard module and assigned to a button:-

Sub TransferData()

Application.ScreenUpdating = False

With Sheet1.[A11].CurrentRegion
           .AutoFilter 16, "Complete"
           .Offset(1).EntireRow.Copy
           Sheet2.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
           .Offset(1).EntireRow.Delete
           .AutoFilter
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

If you don't want the relevant rows of data deleted from the source sheet once each transfer is completed then remove this line of code:-

.Offset(1).EntireRow.Delete

I have used the sheet codes in the above (Sheet1 and Sheet2). If you open the VB Editor, over to the left in the Project Explorer, you will see all your sheets listed with their names in parentheses and, immediately to the left of the sheet names, you will see the sheet codes. Use these in your macros as it is the safest option. Should you ever in future change the sheet names then the code will still work regardless.

I hope that this helps.

Cheerio,
vcoolio.