Copy cell to other workbook, based on value.

[Ilfalco87]

Hello. I have a question regarding vba.

I want to copy cell values 09, 011 and O13 of workbook A, to column D, J and K of workbook B. First I need to find cell value O7 (workbook A) in column A of workbook B. The copied data needs to fill the blank cells of workbook B.

For example, number 11023 can be found in cell number A20. So D20, J20 and K20 need the new data.

I managed to copy paste values to workbook B, but I'm stuck at finding the correct row.

Thank you!

 

[rollis13]

Your request doesn't match your example, so, try my macro for what I understood and if it doesn't meet your needs you can use it as a starting point and edit/change it as you like. The is to be pasted in a vbe module of Workbook A and when run Workbook B must already opened.

Option Explicit
Sub Copy_Find_Paste()
    Dim wb1    As Workbook
    Dim wb2    As Workbook
    Dim sh1    As Worksheet
    Dim sh2    As Worksheet
    Dim fndColA As Range
    Application.ScreenUpdating = False
    Set wb1 = ThisWorkbook
    Set wb2 = Workbooks("Workbook B.xlsx")        '<- change file name as needed
    Set sh1 = wb1.Sheets("Sheet1")                '<- adjust sheet name Workbook A as needed
    Set sh2 = wb2.Sheets("Sheet1")                '<- adjust sheet name Workbook B as needed
    Set fndColA = sh2.Range("A:A").Find(What:=sh1.Cells(7, "O"), LookIn:=xlValues, LookAt:=xlWhole) 'search O7 of Wb1 in Wb2 column A
    If Not fndColA Is Nothing Then                'if found ...
        sh1.Cells(9, "O").Copy sh2.Cells(fndColA.Row, "D")
        sh1.Cells(11, "O").Copy sh2.Cells(fndColA.Row, "J")
        sh1.Cells(13, "O").Copy sh2.Cells(fndColA.Row, "K")
    Else
        MsgBox Cells(7, "O") & " not found in workbook " & wb2.Name
        Exit Sub
    End If
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "Done!"
End Sub

 

[Ilfalco87]

This was perfect thank you! A few tweaks and I've got it working!

 

[rollis13]

Glad having been of some help.
Would be interesting to know which tweaks came up just to realize what I didn't guess.