PDA

View Full Version : Data Merge - Identify Rows Not Pulled Over from Sheet2 into Sheet1 - VBA



CC268
01-19-2017, 11:49 AM
I have two different sheets - Sheet1 and Sheet2. I got some help to write a VBA code that compares the NHA Part Number and Part Number from Sheet 2 (Columns B and Columns F) to the NHA Part Number and Part Number from Sheet 1 (Columns A and Columns D). If those two match then it pulls over the corresponding data in that row in Sheet 2 to Sheet 1. Pretty simple. Here is the code just in case it might help.


Sub MergeDataNew()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False


Dim cell1 As Range, rng1 As Range, cell2 As Range, rng2 As Range
Sheets("Sheet2").Range("A1:BA1").Copy Destination:=Sheets("Sheet1").Range("K1") 'copy column headers from Sheet 2 to Sheet 1
Set rng1 = Sheets("Sheet1").Range("D2:D" & Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, "D").End(xlUp).Row) 'defining variable rng1 -->Sheet1, column D starting at D2 and going to last value in column D
Set rng2 = Sheets("Sheet2").Range("F2:F" & Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "F").End(xlUp).Row) 'defining variable rng2 -->Sheet2, column F starting at F2 and going to last value in column F
For Each cell2 In rng2 'for each cell in range 2 defined above (column F in Sheet 2)...
For Each cell1 In rng1 'for each cell in range 1 defined above (column D in Sheet 1)...
If cell2.Value = cell1.Value And cell2.Offset(0, -4) = cell1.Offset(0, -3).Value Then 'if the value of cell2 equals the value of cell1 AND the value of cell2 (offset by 4 columns) equals the value of cell1 (offset by 3 columns) then...
Sheets("Sheet2").Range("A" & cell2.Row & ":BA" & cell2.Row).Copy _
Destination:=Sheets("Sheet1").Range("K" & cell1.Row & ":BK" & cell1.Row) 'copy corresponding columns A:BA in current row from Sheet 2 and copy into Sheet 1 corresponding columns K:BK in current row
Exit For
End If
Next
Next

Cells.Select
With Selection
.WrapText = False
End With
Columns.AutoFit
Rows.AutoFit

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True


End Sub
However, I need some way to identify which rows were NOT pulled over from Sheet 2 into Sheet 1. Is there a way to do this?
I need to identify those rows that were pulled over from Sheet 2 - that way I can sort the ones that are identified and then copy and paste to the bottom of Sheet 1.

I don't know if maybe there is a command I can throw in the code to have it keep track of which rows it pulled? That way I can manually bring over the rows it didn't pull? I don't know...I am sure I am just dreaming ha.

Thanks!

SamT
01-19-2017, 10:02 PM
Quick and crude:
Change

Sheets("Sheet2").Range("A" & cell2.Row & ":BA" & cell2.Row).Copy _
Dest... Etc
To

With Sheets("Sheet2").Range("A" & cell2.Row & ":BA" & cell2.Row)
.Interior.ColorIndex = 3
.Copy Destination:=Etc
End With
Or to

With Sheets("Sheet2").Range("A" & cell2.Row & ":BA" & cell2.Row)
.Copy Destination:=Etc
.EntireRow.ClearContents
End With