Help With Copy Data Code for Existing and New Records

[Fadil]

Hi,

I have the below listed code that is working alright to copy the existing data from one sheet and paste the records into another one:

What I need help with is how to add a command that will find out if there's a same record existing and just overwrite it without adding a new column in next blank cell in the column.

For example, if a data from "Invoice" sheet cell M16 is existing number in "Bill" sheet Column A, then just overwrite existing one, if it doesn't exists, create a new row.

Currently, the below code just creates a new row.

Sub InvoiceCopy()
' This code copies the Invoice Data and Paste it to Bill Tab
    Sheets("Invoice").Select
    Range("M16:O16").Select
    Selection.Copy
    Sheets("Bill").Select
    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    Range("A" & lMaxRows + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A8").Select
End Sub

 

[dianacris]

Hope this helps.

Sub InvoiceCopy()
Dim val As Variant
Dim lMaxRows As Long
Dim blnReplace As Boolean
Dim i As Long

'GET VALUE TO SEARCH FOR
Sheets("Invoice").Select
val = Sheets("Invoice").Range("M1").Value

'SEARCH FOR VALUE
Sheets("Bill").Select
Sheets("Bill").Range("A1").CurrentRegion.Select
lMaxRows = Selection.Rows.Count
For i = 1 To lMaxRows
  If Sheets("Bill").Range("A" & i).Value = val Then
  blnReplace = True
  Exit For
  End If
Next

' This code copies the Invoice Data and Paste it to Bill Tab
Sheets("Invoice").Select
Range("M1:O1").Select
Selection.Copy
Sheets("Bill").Select
If blnReplace Then
  Range("A" & i).Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Else
  lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
  Range("A" & lMaxRows + 1).Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  Range("A8").Select
End If
End Sub

 

[Fadil]

@ Dianacris
Thank you very much. This code worked perfectly well. I really appreciate your help a lot. I love it when codes are straight forward and no need for any modification as I am terrible sometimes in understanding the codes but I try.

The code worked perfectly fine.

Regards,
Fadil