Author: Oscar Cronquist Article last updated on February 11, 2019

This article demonstrates a macro that copies selected rows based on enabled check boxes. The image above shows data on Sheet1 and checkboxes, some of them are enabled and those will be copied to Sheet2 if you click button "Copy selected rows".

This article is the last and second part, you can read the first part here:Excel vba: Add checkboxes to a sheet (1/2)

The image above shows Sheet2 and records copied from Sheet1.

VBA code

'Name of macro
Sub CopyRows()

'Go through each check box in active sheet
For Each chkbx In ActiveSheet.CheckBoxes
    
    'If check box is enabled
    If chkbx.Value = 1 Then

        'Go through each row on worksheet
        For r = 1 To Rows.Count

            'Check if checkbox is on the same row
            If Cells(r, 1).Top = chkbx.Top Then

                'Simplify syntax
                With Worksheets("Sheet2")

                    'Identify the cell right below the last non empty cell
                    LRow = .Range("A" & Rows.Count).End(xlUp).Row + 1

                    'Copy record from Sheet1 and paste to first empty row on Sheet2
                    .Range("A" & LRow & ":D" & LRow) = _
                    Worksheets("Sheet1").Range("A" & r & ":D" & r).Value
                End With
                
                'Exit For Loop
                Exit For
            End If
        Next r
    End If
Next

End Sub

Where to copy vba code?

  1. Copy above code
  2. Press Alt+F11 in excel
  3. Insert a module
  4. Paste code into code window
  5. Return to excel

I have assigned the macro to a button: Copy selected rows

  1. Go to Developer tab
  2. Click "Insert Controls" button
  3. Click "Button" button
  4. Create the button
  5. Select CopyRows macro
  6. Click OK!

Download Excel file through email

Enter your email address to receive the workbook. Note, an email will actually be sent to you.
* You will also get a weekly newsletter, unsubscribe anytime!