PDA

View Full Version : Solved: Append matched row data from source wsheet back to corresponding row



LLEW1
04-04-2009, 02:54 AM
I have two workbooks (ResultMatch) and (Data Source). Want to search second workbook (Data Source) for row based on matched OrderId (found in each workbook) then with found selected row from (Data Source) paste back to first workbook (ResultMatch) importantly, append found data on same row as a matched OrderId.

Attached is a simplified sample of the workbooks. The example shows intended result in green in the ResultMatch wbook. Would operate from a button located on the ResultMatch wbook.

Any code help really appreciated.

Bob Phillips
04-04-2009, 03:15 AM
Add this to the results workbook



Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim TargetSh As Worksheet
Dim SourceLastRow As Long
Dim sourceLastCol As Long
Dim TargetLastCol As Long
Dim TargetRow As Long

Set TargetSh = Workbooks("DataSource.xls").Worksheets("Sheet1")
With TargetSh

TargetLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With

With ThisWorkbook.Worksheets("Sheet1")

SourceLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
sourceLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
TargetSh.Cells(1, "B").Resize(, sourceLastCol - 1).Copy .Cells(1, sourceLastCol + 1)
For i = 2 To SourceLastRow

TargetRow = 0
On Error Resume Next
TargetRow = Application.Match(.Cells(i, TEST_COLUMN).Value, TargetSh.Columns(1), 0)
On Error GoTo 0
If TargetRow > 0 Then

TargetSh.Cells(TargetRow, "B").Resize(, sourceLastCol - 1).Copy .Cells(i, sourceLastCol + 1)
End If
Next i
End With

End Sub

LLEW1
04-04-2009, 03:32 AM
Thanks xld - works a treat, should get me out of a hole.

As always - really appreciate your help.