Author: Oscar Cronquist Article last updated on January 07, 2019

This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook.

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

How to create button "Save Invoice"

You can find the button in the picture above, there is a red circle around it.

  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

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 add the macro to your workbook

  1. Press Alt-F11 to open visual basic editor
  2. Click Module on the Insert menu
  3. Copy above vba code and paste it to the module. (The code shown in the pic above is another macro not used in this article.)
  4. Exit visual basic editor
Note: Save your workbook with the file extension *.xlsm (Macro-enabled workbook) to save the macro to your workbook. If you don't the macro will be gone the next time you open your workbook.

Download Excel file