Macro to LookUp Data and Copy/Insert only Non-Duplicated Rows
 

This may be an easy one for a VBA guru.

I have to update a production schedule daily by sales order number. I need
to copy and insert non-duplicated rows from Worksheet 2 into Worksheet 1
according to a specific column. For example,

Worksheet 1
Col.A Sales # Col.B Description
1 Description #1
2 Description #2
3 Description #3

Worksheet 2
Col.A Sales # Col.B Description
1 Description #1
4 Description #4
5 Description #5


The macro should only insert the rows with sales # 4 & #5 from Wk Sht. 2 to
Wk Sht 1. Addtionally, the rows should be inserted below the last row with
text in it.

Any help on this would be greatly appreciated! Thank you so much in advance!!

Macro to LookUp Data and Copy/Insert only Non-Duplicated Rows
 

try this code

Sub test()

With Sheets("Sheet1")
.Activate
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set Sh1Range = Range(.Cells(2, "A"), .Cells(LastRow, "A"))
End With

With Sheets("Sheet2")
.Activate
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set Sh2Range = Range(.Cells(2, "A"), .Cells(LastRow, "A"))
End With

NewRow = LastRow + 1
For Each cell In Sh1Range
Set c = Sh2Range.Find(what:=cell.Value, LookIn:=xlValues)
If c Is Nothing Then

cell.EntireRow.Copy Destination:=Rows(NewRow)
NewRow = NewRow + 1
End If

Next cell

End Sub


"RyanH" wrote:

This may be an easy one for a VBA guru.

I have to update a production schedule daily by sales order number. I need
to copy and insert non-duplicated rows from Worksheet 2 into Worksheet 1
according to a specific column. For example,

Worksheet 1
Col.A Sales # Col.B Description
1 Description #1
2 Description #2
3 Description #3

Worksheet 2
Col.A Sales # Col.B Description
1 Description #1
4 Description #4
5 Description #5


The macro should only insert the rows with sales # 4 & #5 from Wk Sht. 2 to
Wk Sht 1. Addtionally, the rows should be inserted below the last row with
text in it.

Any help on this would be greatly appreciated! Thank you so much in advance!!

Macro to LookUp Data and Copy/Insert only Non-Duplicated Rows
 

I think this works! I will double check it manually on our print out of the
schedule. Thank You So Much for such a quick response!!


"Joel" wrote:

try this code

Sub test()

With Sheets("Sheet1")
.Activate
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set Sh1Range = Range(.Cells(2, "A"), .Cells(LastRow, "A"))
End With

With Sheets("Sheet2")
.Activate
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set Sh2Range = Range(.Cells(2, "A"), .Cells(LastRow, "A"))
End With

NewRow = LastRow + 1
For Each cell In Sh1Range
Set c = Sh2Range.Find(what:=cell.Value, LookIn:=xlValues)
If c Is Nothing Then

cell.EntireRow.Copy Destination:=Rows(NewRow)
NewRow = NewRow + 1
End If

Next cell

End Sub


"RyanH" wrote:

This may be an easy one for a VBA guru.

I have to update a production schedule daily by sales order number. I need
to copy and insert non-duplicated rows from Worksheet 2 into Worksheet 1
according to a specific column. For example,

Worksheet 1
Col.A Sales # Col.B Description
1 Description #1
2 Description #2
3 Description #3

Worksheet 2
Col.A Sales # Col.B Description
1 Description #1
4 Description #4
5 Description #5


The macro should only insert the rows with sales # 4 & #5 from Wk Sht. 2 to
Wk Sht 1. Addtionally, the rows should be inserted below the last row with
text in it.

Any help on this would be greatly appreciated! Thank you so much in advance!!

Macro to LookUp Data and Copy/Insert only Non-Duplicated Rows
 

I performed the macro and it seemed to work beautifully except the first line
on Sheet 2 is not showing up on Sheet 1. This is the only line not being
inserted, do you know why? Thanks again for all the help!

"Joel" wrote:

try this code

Sub test()

With Sheets("Sheet1")
.Activate
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set Sh1Range = Range(.Cells(2, "A"), .Cells(LastRow, "A"))
End With

With Sheets("Sheet2")
.Activate
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set Sh2Range = Range(.Cells(2, "A"), .Cells(LastRow, "A"))
End With

NewRow = LastRow + 1
For Each cell In Sh1Range
Set c = Sh2Range.Find(what:=cell.Value, LookIn:=xlValues)
If c Is Nothing Then

cell.EntireRow.Copy Destination:=Rows(NewRow)
NewRow = NewRow + 1
End If

Next cell

End Sub


"RyanH" wrote:

This may be an easy one for a VBA guru.

I have to update a production schedule daily by sales order number. I need
to copy and insert non-duplicated rows from Worksheet 2 into Worksheet 1
according to a specific column. For example,

Worksheet 1
Col.A Sales # Col.B Description
1 Description #1
2 Description #2
3 Description #3

Worksheet 2
Col.A Sales # Col.B Description
1 Description #1
4 Description #4
5 Description #5


The macro should only insert the rows with sales # 4 & #5 from Wk Sht. 2 to
Wk Sht 1. Addtionally, the rows should be inserted below the last row with
text in it.

Any help on this would be greatly appreciated! Thank you so much in advance!!