CODE for COPYING rows according to specified CRITERIA and paste it to another workbook

Hi Everyone,

I have 2 workbooks namely Masterlist.xlsx and REGULAR.xlsx both have the sheet named LIST with the following formats

A B C D E
1 EMPLOYEE NUMBER NAME LOCATION DATE OF HIRE STATUS
2 123578 Michael Jordan New York 01/18/11 Regular
3 321666 Kobe Bryant LA 02/31/13 Prob


What I want to do is to copy A2:E2 from the Masterlist.xlsx if the value of E2 is "Regular" or blank. Then paste it to the workbook REGULAR.slxs at the bottom row so I wont overwrite existing data. Additionally, I dont want to paste rows that was already pasted after rerunning the code multiple times.

I badly need your help.

Thanks a lot!

This may work for the first part.

Sub kobeXreborn()
Application.ScreenUpdating = False
Range("E2:E" & ActiveSheet.UsedRange.Rows.count).AutoFilter 1, "Regular", xlAnd, ""
Range("A2:E" & ActiveSheet.UsedRange.Rows.count).SpecialCells(xlCellTypeVisible).Copy Workbooks("REGULAR.xlsx").Sheets("List").Range("A" & Rows.count).End(3)(2)
Application.ScreenUpdating = True
End Sub

If Masterlist is continuously compiled and REGULAR can be cleared before pasting it can be modified to handle the second part is well.

John,

That works good to me.

But is there a way I dnt need to use filter? Everytime it paste I just want the code to check if the row containing the data is already existing on the REGULAR.xlsx. If it does it shoudnt paste anymore and proceed to the next row until the last row.

Thank your John I appreciate your help!

Sorry, I didn't get back to you sooner. If there's a match for Employee ID in REGULAR will that mean not to copy? Also can you attach a sample file?

John,

Sorry the file is too huge.

I mean the entire row John. If the rows from (Masterlist.xlsx) that is to be copied is already existing in the REGULAR.xlsx it shouldn't copy that row anymore. Its like omitting the rows or not copying the rows that already exist in the Masterlist.xlsx.

Thanks JOHN.

Maybe:

Sub kobeXrebornz()
Application.ScreenUpdating = False
Workbooks("Masterlist.xls").Activate
Sheets("Sheet1").Activate
Columns("B:B").Insert
With Range("B2:B" & ActiveSheet.UsedRange.Rows.count + 1)
    .Formula = "=VLOOKUP(A2,[Regular.xls]Sheet1!$A$2:$B$" & Workbooks("Regular.xls").Sheets("Sheet1").UsedRange.Rows.count & ",1,FALSE)"
    .Value = .Value
    .Replace "#N/A", ""
End With
For Each rcell In Range("B2:B" & ActiveSheet.UsedRange.Rows.count + 1)
    If rcell.Value = "" And rcell.Offset(, 4).Value = "Regular" Then
        rcell.Offset(, -1).Copy Workbooks("Regular.xls").Sheets("Sheet1").Range("A" & Rows.count).End(3)(2)
        Range(rcell.Offset(, 1), rcell.Offset(, ActiveSheet.UsedRange.Columns.count)).Copy Workbooks("Regular.xls").Sheets("Sheet1").Range("B" & Rows.count).End(3)(2)
    End If
    If rcell.Value = "" And rcell.Offset(, 4).Value = "" Then
        rcell.Offset(, -1).Copy Workbooks("Regular.xls").Sheets("Sheet1").Range("A" & Rows.count).End(3)(2)
        Range(rcell.Offset(, 1), rcell.Offset(, ActiveSheet.UsedRange.Columns.count)).Copy Workbooks("Regular.xls").Sheets("Sheet1").Range("B" & Rows.count).End(3)(2)
    End If
Next rcell
Columns("B:B").Delete
Application.ScreenUpdating = True
End Sub