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.

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

11

SHIFT

DOCK DOOR

DATE RCV'D

VENDOR

TRL #

CASES

PLTS

UNLOADED START

UNLOAD FINISHED

ASN #

UNLOADER

Load Type

PO

SCANNER

STATUS

12

2nd

119

11/18

Central Aquatics

245

4522

34

1730

1925

2439

Artimus

C

123345

SIMS

COMPLETE

13

2nd

120

11/18

Shandong

58R5

3245

52

1752

2230

2372

Delta

L

3456836

SMITH

<tbody>
</tbody>

 

[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.