Loop/Compare help

[jrevard]

So it has been a while and I was never a pro at this, but here's my code and what's happening.

Things are going well and the first instance found is pasted in the correct position, but the subsequent pastings are put in the first row (see screen shots). I am using PasteSpecial b/c Paste would not work; I got the error "Object does not support this property or method." Maybe the pasting is my problem.

Also, I would like to insert the data if it is not found in the other sheet. It will be sorted numerically by column A. I have not even researched possible solutions to this one.

Further, any suggestions on cleaning/simplifying the code are appreciated.

Sub RoundedRectangle1_Click()
    
    Dim recent As Worksheet
    Dim original As Worksheet
    Dim criteria As String
    Dim count As Integer
    
    Set recent = Worksheets("Recent")
    Set original = Worksheets("Invoices")
    
    recent.Select
    recent.Range("A1").Select
    
    Do Until IsEmpty(ActiveCell)
        criteria = ActiveCell.Value
        original.Select
        original.Range("A1").Select
        Do Until IsEmpty(ActiveCell)
            If criteria = ActiveCell.Value Then
            recent.Select
            recent.Range(ActiveCell, ActiveCell.Offset(0, 2)).Select
            Selection.Copy
            original.Select
            original.Cells(ActiveCell).PasteSpecial
            count = count + 1
            End If
            ActiveCell.Offset(1, 0).Select
        Loop
        recent.Select
        ActiveCell.Offset(1, 0).Select
    Loop
    
End Sub

 

[Tom Urtis]

How about posting a screen shot of what your expected results are.

 

[jrevard]

Thanks for the reply Tom!

Notice that the "Invoices" sheet in the original post has changed to exclude the number 3. This is to show what should happen if the object that the code is trying to replace does not exist in the original invoice sheet.

The image in this post is what should happen if the "Recent" sheet updates the "Invoice" sheet.

 

[jrevard]

Sorry, here is a pic of what it is doing now.

 

[Tom Urtis]

I assume you are attaching the macro to the large blue button on the Recent sheet. If so, attach this macro and see if it does what you want.

Sub Test1()
Application.ScreenUpdating = False
Dim NextRow&, cell As Range, i%, varFind As Variant
NextRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
Set varFind = Nothing
With Sheets("Invoices")
For Each cell In Columns(1).SpecialCells(2)
Set varFind = _
.Columns(1).Find(What:=cell.Value, LookIn:=xlFormulas, LookAt:=xlWhole)
If varFind Is Nothing Then
For i = 1 To 2
.Cells(NextRow, i).Value = Cells(cell.Row, i).Value
Next i
NextRow = NextRow + 1
Else
.Cells(varFind.Row, 2).Value = cell.Offset(0, 1).Value
End If
Set varFind = Nothing
Next cell
.Range("A1").CurrentRegion.Sort Key1:=.Range("A1"), _
Order1:=xlAscending, _
Header:=xlNo
End With
Application.ScreenUpdating = True
End Sub