Author: Oscar Cronquist Article last updated on August 11, 2022

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 press with left mouse button on 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)

1. Worksheet data

The image above shows Sheet2 and records copied from Sheet1.

Back to top

2. 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

Back to top

3. Where to copy vba code?

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

Back to top

4. How to run the macro using a button

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

  1. Go to Developer tab
  2. Press with left mouse button on "Insert Controls" button
  3. Press with left mouse button on "Button" button
  4. Create the button
  5. Select CopyRows macro
  6. Press with left mouse button on OK!

Back to top

 

Back to top