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. Press with left mouse button on "Developer" tab on the ribbon
  2. Press with left mouse button on "Insert Controls" button
  3. Press with left mouse button on "Button (Form Control)"
  4. Create a button on sheet
  5. Type a macro name "Button2_Press with left mouse button on()"
  6. Press with left mouse button on OK

Vba code

Sub Button2_Press with left mouse button on()
  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. Press with left mouse button on 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.

Get the Excel file