See if it helps If You have Name Range("A:A") in Sheet1 then Use this Code
Sub CopyIfNotExist() Dim rng1 As Range Dim rng2 As Range Dim cell1 As Range Dim cell2 As Range Dim Flag As Boolean Set rng1 = Sheets("Sheet1").Range("A2:A" & Sheets("Sheet1").UsedRange.Rows.Count) Set rng2 = Sheets("Sheet2").Range("A2:A" & Sheets("Sheet2").UsedRange.Rows.Count) For Each cell1 In rng1 Flag = False For Each cell2 In rng2 If cell2.Value = cell1.Value Then Flag = True Exit For End If Next If Flag = True Then Else cell1.EntireRow.Copy Sheets("Sheet2").Range("A" & Sheets("Sheet2").UsedRange.Rows.Count + 1) End If Next End Sub -----Original Message----- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Dkin Sent: Monday, July 25, 2011 7:41 AM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ Copy Variable data The below code works and return on single name match. Basically copies the data from sheet one to sheet two using Name as a key. Now would like create loop to go through both sheets and compare the names, and if the name exist in sheet one then copy his value into sheet two. Please let me know the possibilities. Sheet one: "Cargo" Cargo Summary Request Total 1 Total 2 Lori Trump Susan doo Dii Kon 888 111 Moo Kevin Ajax James Alex Trapek Kevin O'neil Ming Kii Kung Kwan Dii Kii Nung No Li Morgan Total Sheet2:" ORDER" New Cargo Cargo Captain Sea Name Total A Total B General ZW N/A Lori Trump 122 66 General DE N/A Moris Bee 40 56 General DD N/A Dii Kon 888 78 General DD N/A Moo Kevin 127 99 General DD N/A Ajax James 24 33 General DD N/A Alex Trapek 231 12 General MM N/A Kevin O'neil 0 10 General Total Reeefer HU N/A Dii Kon 0 33 Reefer HU N/A Kevin O'neil 515 55 Reefer Total 515 Fish cargo NI N/A Moris Bee 0 67 Fish cargo NI N/A Ajax James 0 83 Fish cargo Total Here the code: Option Explicit Option Compare Text Public Captain As String Public LastRow As Double Public Variable(15) As Variant Public VarTemp(15) As Variant Sub Cargo_Data() Dim iCol As Long Dim x As Long, x1 As Long, x2 As Long Dim NewBook As Workbook Dim bFind As Boolean Dim Name As String Dim iTmp As Long Worksheets("Cargo").Select 'clear the array variable For x1 = 1 To 15 Variable(x1) = 0 VarTemp(x1) = 0 Next x1 Captain = Name If Captain <> " " Then ' GoTo Name End If ActiveWorkbook.Sheets("Order").Select ' Worksheets("Order").Select Range("A6").Select Selection.End(xlDown).Select LastRow = ActiveCell.Row For x1 = 6 To LastRow If Cells(x1, 4) = "Dii Kon" Then Call AddTo(Variable(2), Cells(x1, 5)) Call AddTo(Variable(3), Cells(x1, 6)) End If Next x1 '--------------------------------------------------------------------------- --------------------------- 'Imports to the data Worksheets("Cargo").Select Range("A7").Select Selection.End(xlDown).Select LastRow = ActiveCell.Row For x1 = 7 To LastRow If Cells(x1, 1) = "Dii Kon" Then For iCol = 2 To 15 Select Case iCol Case 2 To 3 Cells(x1, iCol) = Variable(iCol) End Select Next iCol Exit For End If Next x1 End Sub Sub AddTo(ByRef vValue As Variant, vNew As Variant) If IsNumeric(vNew) Then If IsNumeric(vValue) Then vValue = vValue + vNew ElseIf vValue = "na" Or vValue = "" Then vValue = vNew End If ElseIf vNew = "na" Then If vValue = 0 Then vValue = "na" End If End If End Sub -- ---------------------------------------------------------------------------- ------ Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel