macro for copy data Workbook1 and paste to Workbook2

[santosh226001]

Sir
i m looking a macro for my project..

Where 2 excel workbook (Ex, workbook1 & workbook2 )..

We want to copy data from workbook 2 to workbook 1

in workkbook1 Sheet name (RD-1)
in workkbook2 Sheet name (RD-2)

Workbook1 (Sheet RD-1)

A B C D
item1 10 12 120
item2 5 8 40
item3 15 6 90



Workbook2 (Sheet RD-2)
A B C D
item1 10 12 120
item2 5 8 40
item3 15 6 90
item4 20 10 200
item5 170 5 850
item6 15 5 75



After Run Macro Resulted Workbook1 (Sheet RD-1)
A B C D
item1 10 12 120
item2 5 8 40
item3 15 6 90
item4 20 10 200
item5 170 5 850
item6 15 5 75



Actually if workbook2 item already add in workboko1 then then record not copy... in examle only copy item 4,5,6 coz itme 1,2,3 already in workbook1


hope u friends & forum guru help me solve this problem..

Thanks in advance

Santosh

[nilem]

Hi Santosh,
I have two options for you

Sub ertert() ' for Excel 2003, 07, 10 etc.
Dim x, i&, j&, k&
With Workbooks("Book1.xls").Sheets("RD-1")
    x = .Range("A1:D" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
End With

With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    For i = 1 To UBound(x)
        .Item(x(i, 1)) = 1
    Next i
    With Workbooks("Book2.xls").Sheets("RD-2")
        x = .Range("A1:D" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
    End With
    For i = 1 To UBound(x)
        If Not .Exists(x(i, 1)) Then
            k = k + 1
            For j = 1 To UBound(x, 2): x(k, j) = x(i, j): Next j
        End If
    Next i
End With: If k = 0 Then Exit Sub
With Workbooks("Book1.xls").Sheets("RD-1")
    .Cells(Rows.Count, 1).End(xlUp)(2).Resize(k, UBound(x, 2)).Value = x
End With
End Sub

Sub ert() ' for Excel 2007, 10 etc.
Dim x: Application.ScreenUpdating = False
With Workbooks("Book2").Sheets("RD-2")
    x = .Range("A1:D" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
End With
With Workbooks("Book1").Sheets("RD-1")
    With .Cells(Rows.Count, 1).End(xlUp)(2).Resize(UBound(x, 1), UBound(x, 2))
        .Value = x: .CurrentRegion.RemoveDuplicates Columns:=1
    End With
End With: Application.ScreenUpdating = True
End Sub

both files ('Book1' and 'Book2') must be open

[santosh226001]

Thank u so much sir for help me but.. after running this macro (1st one) a msg box display in vba code subscript out of range whenever both file book1 & book both open

i m copying ur code in book1 is it ok?

[nilem]

i m copying ur code in book1 is it ok?

yeah, right
to take into account all possible errors in the files, one need to write a branched error handler in the code. This will increase the code multiple times. Therefore it will be better if you attach the sample files Book1 and Book2
Edited
Oh, I am writing in English for too long

[santosh226001]

Sorry Nilim sir it's works.. actually after saving both file when we try that run..

Thank you so much for above macro thanks

[santosh226001]

Nilim sir is it possible if we not define book1 name & macro automatically read workbook name which one we run macro..??? coz some time we change workbook name in saving time so again change name in macro...only book1 name condtion.. pls

[nilem]

try it

Sub ertert2()    ' for Excel 2003, 07, 10 etc.
Dim x, i&, j&, k&, wb As Workbook
With ThisWorkbook.Sheets("RD-1")
    x = .Range("A1:D" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
End With
With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    For i = 1 To UBound(x)
        .Item(x(i, 1)) = 1
    Next i
    For Each wb In Workbooks
        If wb.Name <> ThisWorkbook.Name Then
            With wb.Sheets("RD-2")
                x = .Range("A1:D" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
            End With
        End If: Exit For
    Next wb
    For i = 1 To UBound(x)
        If Not .Exists(x(i, 1)) Then
            k = k + 1
            For j = 1 To UBound(x, 2): x(k, j) = x(i, j): Next j
        End If
    Next i
End With: If k = 0 Then Exit Sub
With ThisWorkbook.Sheets("RD-1")
    .Cells(Rows.Count, 1).End(xlUp)(2).Resize(k, UBound(x, 2)).Value = x
End With
End Sub

In this case must be opened only two of your books

[santosh226001]

ok sir but if we want to compare insted of Colum A value ..... we compare other column like B or C then where we change.. pls suggest me..also thanks

[nilem]

...we compare other column like B

...
    For i = 1 To UBound(x)
        .Item(x(i, 2)) = 1
    Next i
    ...
        For i = 1 To UBound(x)
        If Not .Exists(x(i, 2)) Then
    ...

...we compare other column like C

...
    For i = 1 To UBound(x)
        .Item(x(i, 3)) = 1
    Next i
    ...
        For i = 1 To UBound(x)
        If Not .Exists(x(i, 3)) Then
    ...