Sub Copy_Check_Data()
Dim Found As Range, FirstFound As String, rngCheck As Range, counter As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Sheet1") 'Destination worksheet
Set ws2 = Sheets("Sheet2") 'Source worksheet
Application.ScreenUpdating = False
For Each rngCheck In ws1.Range("C:C").SpecialCells(xlCellTypeConstants, xlNumbers)
If IsEmpty(rngCheck.Offset(, 5)) Then
Set Found = ws2.Range("C:C").Find(What:="Check No." & rngCheck.Value, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Found Is Nothing Then
FirstFound = Found.Address
Do
If Found.Offset(, 5).Value = rngCheck.Offset(, 1).Value Then
Found.Offset(, -2).Resize(, 8).Copy Destination:=rngCheck.Offset(, 5)
counter = counter + 1
Exit Do
End If
Set Found = ws2.Range("C:C").FindNext(After:=Found)
Loop Until Found.Address = FirstFound
End If
End If
Next rngCheck
Application.ScreenUpdating = True
MsgBox counter & " checks copied. ", vbInformation, "Copy Check Data Complete"
End Sub
Bookmarks