Search Destination wb Column, MATCH value then copy data from Source wb into Destination Row

[Hitechtoys]

I have looked through dozens of threads and have attempted to implement several with varying results.
I am an intermediate Excel user but new to VBA. I'm looking for VBA code to accomplish
the following task since I can't do it with a formula.


I have two different workbooks. I have a button placed in the Source workbook. When clicked, I'd like the
button to look in the Destination workbook in Column A to Match value with a cell (H13) in the Source wb.


When it finds the matching number, populate the Destination wb row with the cells below from the Source wb.


The Source wb cell (H13) is looking for a Match in the Destination wb in Column A. Once it finds a Match,
then copy the cells below into the correct row in the Destination wb.


Source wb cells to copy to the Destination wb row in [column]: C15 [L], C17 [E], C19 [F],
H15 [D], H19 [G], G21 , K21 [C], B22 [H], C33 [M], K33 [J], B34 , B54 [K]


The Destination wb can have 400+ rows to search in column A to locate a match.


I think I got the farthest with this code but my cells aren't reporting correctly...

Sub VBA_Read_External_Workbook()
 
    Dim Target_Workbook As Workbook
    Dim Source_Workbook As Workbook
    Dim Target_Path As String
    Dim Source_Data As String
    
    Target_Path = "C:\Test\"
    Set Target_Workbook = Workbooks.Open("C:\Test\BIM_Notice_Log2.xlsm")
    Set Source_Workbook = ThisWorkbook
    
    'Need to add a 'MATCH VALUE' here?  Match value in column A then populate the Row
    'This will only do one row at a time... do I need a .Range statement?


    Target_Data = Target_Workbook.Sheets(1).Cells(34, 2)(33, 3)(33, 11)(54, 2)
    Source_Workbook.Sheets(1).Cells(1, 9)(1, 13)(1, 10)(1, 11) = Target_Data
    
    'Update Target File
    Source_Data = Source_Workbook.Sheets(1).Cells(1, 9)(1, 13)(1, 10)(1, 11)
    Target_Workbook.Sheets(1).Cells(34, 2)(33, 3)(33, 11)(54, 2) = Source_Data
    
    'Close Target Workbook
    Source_Workbook.Save
    Target_Workbook.Save
    Target_Workbook.Close False
    
    'Process Complete
    MsgBox "Update Completed"
    
End Sub

Is this possible? Maybe I'm asking too much? After a week of searching threads and trying various attempts I thought I'd post it here.

Thank you in advance for your help or any input you can give!!

 

[Norie]

Does where the matching value is found in the source workbook have any bearing on which cells to copy?

By the way, what is something like this meant to refer to?

Target_Data = Sheets(1).Cells(34, 2)(33, 3)(33, 11)(54, 2)

If I try that code it appears to return a value from $O$151 on Sheet1.

 

[Hitechtoys]

Does where the matching value is found in the source workbook have any bearing on which cells to copy?

By the way, what is something like this meant to refer to?

Target_Data = Sheets(1).Cells(34, 2)(33, 3)(33, 11)(54, 2)

If I try that code it appears to return a value from $O$151 on Sheet1.

"Hi Norie, Thanks for your response!
I thought that this was the way to READ the data (cells) from the Destination wb and the Source:

Target_Data = Target_Workbook.Sheets(1).Cells(3, 8) '(33, 3)(33, 11)(54, 2)
    Source_Workbook.Sheets(1).Cells(34, 2) = Target_Data  '(1, 13)(1, 10)(1, 11)

And I thought this is how to WRITE - update/copy the cells to the source:

Source_Data = Source_Workbook.Sheets(1).Cells(34, 2) '(1, 13)(1, 10)(1, 11)
    Target_Workbook.Sheets(1).Cells(3, 8) = Source_Data  '(33, 3)(33, 11)(54, 2)

I was having problems with getting the cells to cooperate... especially since you said it returned a value to $O$151. That's definitely wrong. I thought I was setting all of the cells to update to the other wb.

Does where the matching value is found in the source workbook have any bearing on which cells to copy? Yes... I am hoping to match the value in whatever row in column A, then copy/update all of the cells in that row with the data from the source wb.

Basically trying to update one workbook from a source wb using a button click. Currently I am cutting and pasting every cell from one wb into the other. I placed a couple of images in dropbox... hope you can see them. Again... new to this format. Thanks for your patience!

 

[Norie]

You won't be able to copy the data from the source workbook in batchs both the source and destination are non-contiguous, so you will need to go cell by cell.

That might not be as tedious as it sounds, if there is a pattern/some logic/magic that can be used to determine which cells to copy.

PS Unfortunately the images didn't come through, for me anyway.