To transfer data between worksheets in VBA by copying and pasting only the values that correspond to matching ID's, you can follow these steps:
Identify the columns that contain the matching ID's in both the source and destination worksheets. For example, if you have a source worksheet named "Data" with columns A, B, and C, and a destination worksheet named "Results" with columns D, E, and F, and the ID's are in column A of both worksheets, then you can use column A as the matching ID.
Loop through each row of the source worksheet and compare the ID of the current row with the ID's in the destination worksheet to find a match.
If a match is found, copy the values from the columns that you want to transfer (e.g., columns B and C in the source worksheet) and paste them as values in the corresponding columns of the destination worksheet (e.g., columns E and F).
Here is a sample code:
Sub TransferData()
Dim srcRow As Long, dstRow As Long, lastSrcRow As Long, lastDstRow As Long
Dim srcID As Variant, dstID As Variant
' Set the starting row numbers and last row numbers for the source and destination worksheets
srcRow = 2 ' start copying from row 2 (assuming row 1 contains header)
dstRow = 2 ' start pasting to row 2 (assuming row 1 contains header)
lastSrcRow = Worksheets("Data").Cells(Rows.Count, 1).End(xlUp).Row ' find the last row in column A of the source worksheet
lastDstRow = Worksheets("Results").Cells(Rows.Count, 4).End(xlUp).Row ' find the last row in column D of the destination worksheet
' Loop through each row of the source worksheet
For srcRow = 2 To lastSrcRow ' change 2 to the starting row number of your data
srcID = Worksheets("Data").Cells(srcRow, 1).Value ' get the ID from column A of the source worksheet
' Loop through each row of the destination worksheet to find a match for the ID
For dstRow = 2 To lastDstRow ' change 2 to the starting row number of your data
dstID = Worksheets("Results").Cells(dstRow, 4).Value ' get the ID from column D of the destination worksheet
' If a match is found, copy the values from columns B and C of the source worksheet and paste as values in columns E and F of the destination worksheet
If srcID = dstID Then
Worksheets("Results").Cells(dstRow, 5).Value = Worksheets("Data").Cells(srcRow, 2).Value ' copy column B values from source and paste as values in column E of destination
Worksheets("Results").Cells(dstRow, 6).Value = Worksheets("Data").Cells(srcRow, 3).Value ' copy column C values from source and paste as values in column F of destination
Exit For ' exit the loop to avoid unnecessary comparisons
End If
Next dstRow
Next srcRow
End Sub
In this example, the code assumes that:
Asked: 2023-06-29 05:31:48 +0000
Seen: 10 times
Last updated: Jun 29 '23