Author: Oscar Cronquist Article last updated on January 02, 2018

This post describes how to copy values between sheets. I am using the invoice template sheet.

This macro copies rows containing values. Blank rows are not copied. Values are copied from sheet "Invoice" to sheet "Invoice data".

Check out the Invoices archive.

How to create button "Save Invoice"

  1. Click "Developer" tab on the ribbon
  2. Click "Insert Controls" button
  3. Click "Button (Form Control)"
  4. Create a button on sheet
  5. Type a macro name "Button2_Click()"
  6. Click OK

Check out the Form Controls archive to learn more.

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
  i = 1
  Set rng_dest = Sheets("Invoice data").Range("D:G")
  ' Find first empty row in columns D:G on sheet Invoice data
  Do Until WorksheetFunction.CountA(rng_dest.Rows(i)) = 0
    i = i + 1
  'Copy range B16:I38 on sheet Invoice to Variant array
  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

Save invoice data1.xls
(Excel 97-2003  Workbook *.xls)