Hi, I recently wrote someone a solution as shown below. The OP wanted to
compare values in col A on two sheets -- Source and Dest. If the values were
equal on any given row, he wanted contents from Columns I and K to be copied
from Source to Dest for that row (to I and K, respectively).

Because the ranges copied from are staggered and the ranges copied to are
non-contiguous, I saw no way to add the cells to a range for bulk
copy/paste -- so of course the solution takes a long time (the user said
about 35,000 rows to check, but I am not sure how many cop/pastes would have
resulted)

Can the code be made more efficient in this case?

Thanks!

Sub CopyIdenticals()
Dim rngSourceCompare As Range
Dim c As Range

On Error Resume Next
Set rngSourceCompare = Application.InputBox _
(prompt:="Select all cells in col A for comparison", _
Type:=8)
If rngSourceCompare Is Nothing Then
Exit Sub
End If

If rngSourceCompare.Parent.Name < "Source" Then
MsgBox "Only choose col A values on sheet 'Source'"
Exit Sub
End If

On Error GoTo 0
Application.ScreenUpdating = False
For Each c In rngSourceCompare

If c.Value = Sheets("Dest").Range(c.Address).Value Then
c.Offset(0, 8).Copy 'note: Col I
Sheets("Dest").Range("I" & c.Row).PasteSpecial _
Paste:=xlPasteValues
c.Offset(0, 10).Copy 'note: Col K
Sheets("Dest").Range("K" & c.Row).PasteSpecial _
Paste:=xlPasteValues
End If
Next c
Application.ScreenUpdating = True
End Sub