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 NameTotal 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'neil0 10
General Total
Reeefer HU N/A Dii Kon 0 33
Reefer HU N/A Kevin O'neil515 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 CaptainThen
' 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
