Copy data from one excel workbook to another workbook

Question

For example if I want to copy data ("B1:B8") from workbook1 sheet 1 and paste it into ("D1:D8") of another workbook sheet 1 but this has to done by referring or comparing the cells (A1:A8) of book 1 and cells (C1:C8) has only same values then paste else skip or do nothing.

Example: Book1 Sheet1 I had lined up;

COL A   COL B
app     yes
conf    pass
gif     no
pic     fail
bit     yes
map     yes
conf    yes
bit     no

Now in Workbook 2 Sheet 1 I was given in COL C as,

COL C
app
conf
gif
pic
gif
pic
bit
gif

So in COL D I have to paste values only for those COL A and COL C equal ones if those were not equal skip or paste nothing in COL D

I have written code something like this but unfortunately its pasting everything!!

Sub Copy_range()
Dim x As Workbook
Dim y As Workbook
Dim rng As Range
Dim c As Range
Dim i As Long

Set x = ActiveWorkbook
Set y = Workbooks.Open(x.Sheets(1).Range("G1"))

Set rng = x.Sheets(1).Range("A1:A8")
Set c = y.Sheets(1).Range("C1:C8")

  For i = 1 To i + 1


 If x.Sheets(1).Range("A1:A8").End(xlUp).Row = y.Sheets(1).Range("C1:C8").End(xlUp).Row Then

 x.Sheets(1).Range("B1:B8").Copy
 y.Sheets(1).Range("D1:D8").PasteSpecial

 y.Close
 End If
Next

End Sub

This sentence makes my head hurt: `So in COL D I have to paste values only for those COL A and COL C equal ones if those were not equal skip or paste nothing in COL D` — Geoff, May 19 '14 at 18:52
I am sorry, In COL D ("D1:D8") paste values from Workbook 1 Sheet 1 COL B ("B1:B8") only if Col A (workbook 1 sheet 1) equals to COL C (Workbook 2 Sheet 1) if those were not equal then paste nothing — user3520101, May 19 '14 at 18:55

Geoff · Answer 1 · 2014-05-19T20:32:01.023

It looks like you're trying to do a looking from one range to another? If so, you can use something like the following to lookup each value in column C against your master values in columns A and B:

Sub LookupRange()
    On Error Resume Next
    For i = 1 To 8
        ActiveSheet.Range("D" & i) = _
            Application.WorksheetFunction.VLookup( _
                ActiveSheet.Range("C" & i), _
                ActiveSheet.Range("A1:B8"), _
                2, _
                False)
    Next i
End Sub

That will loop through cells C1..C8 and look up each value in cells A1..A8. If a match is found then it will copy the corresponding value to column D.

For your example above you'd get:

enter image description here

All you need to do then is alter the code to work with separate worksheets.

kris · Answer 2 · 2014-08-31T17:21:54.877

Sub CopyInput2Output()

    Dim wbkSRC As Workbook
    Dim wbkDES As Workbook
    Dim strNameSheetSRC As String
    Dim strNameSheetDES As String

    'strSrcFile = "C:\src.xls"
    'strDesFile = "C:\des.xls"
    Set wbkSRC = Workbooks.Open(strSrcFile)
    Set wbkDES = Workbooks.Open(strDesFile)
    'Set wbkSRC = ThisWorkbook
    'Set wbkDES = ThisWorkbook

    strNameSheetSRC = 1   '  "input"
    strNameSheetDES = 1   '  "output"



    ' your selection : Sheets(1)
    wbkSRC.Worksheets(strNameSheetSRC).Range("A1:A8").Copy

    ' your selection : Sheets(1)
    With wbkDES.Worksheets(strNameSheetSRC)
        Range("C1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    End With

    MsgBox ("Just a check : CopyInput2Output()")

End Sub

Copy data from one excel workbook to another workbook

2 Answers2