In a previos post:Excel vba: Save invoice data we added/copied data between sheets.

This post describes how to overwrite existing values with new values.

If invoice number already exists, a message box asks if you want to overwrite old values with new values.

VBA code:

Sub Button2_Click()

  Dim rng As Range
  Dim i As Long
  Dim a As Long
  Dim rng_dest As Range

  Application.ScreenUpdating = False

  'Check if invoice # is found on sheet "Invoice data"

  i = 1

  Do Until Sheets("Invoice data").Range("A" & i).Value = ""

    If Sheets("Invoice data").Range("A" & i).Value = Sheets("Invoice").Range("D13").Value Then

      'Ask overwrite invoice #?

      If MsgBox("Overwrite invoice data?", vbYesNo) = vbNo Then

        Exit Sub

      Else

        Exit Do

      End If

    End If

    i = i + 1

  Loop

  i = 1

  Set rng_dest = Sheets("Invoice data").Range("D:G")

  'Delete rows if invoice # is found

  Do Until Sheets("Invoice data").Range("A" & i).Value = ""

    If Sheets("Invoice data").Range("A" & i).Value = Sheets("Invoice").Range("D13").Value Then

      Sheets("Invoice data").Range("A" & i).EntireRow.Delete

      i = 1

    End If

    i = i + 1

  Loop

  ' Find first empty row in columns D:G on sheet Invoice data

  Do Until WorksheetFunction.CountA(rng_dest.Rows(i)) = 0

    i = i + 1

  Loop

  'Copy range B16:I38 on sheet Invoice

  Set rng = Sheets("Invoice").Range("B16:E38")

  ' Copy rows containing values to sheet Invoice data

 For a = 1 To rng.Rows.Count

    If WorksheetFunction.CountA(rng.Rows(a)) <> 0 Then

      rng_dest.Rows(i).Value = rng.Rows(a).Value

      'Copy Invoice number

      Sheets("Invoice data").Range("A" & i).Value = Sheets("Invoice").Range("D13").Value

      'Copy Date

      Sheets("Invoice data").Range("B" & i).Value = Sheets("Invoice").Range("F3").Value

      'Copy Company name

      Sheets("Invoice data").Range("C" & i).Value = Sheets("Invoice").Range("B8").Value

      i = i + 1

    End If

  Next a

  Application.ScreenUpdating = True

End Sub

How to implement macro in excel

  1. Press Alt-F11 to open visual basic editor
  2. Click Module on the Insert menu
  3. Copy and paste above vba code
  4. Exit visual basic editor

Download excel example file

Edit invoice data.xls
(Excel 97-2003  Workbook *.xls)