Macro using checkboxes to designate rows to be copied over into next sheet at push of a button

[Xc3l3r4t0r]

Hi all, appreciate any help you can muster.

I am looking to deploy a macro that would utilize checkboxes built into every row of data, that would enable the user to select specific rows to be copied over into the next sheet. The source sheet (Sheet 1) has 24 rows of data (through column X) and unique data begins in row 2 with column headers in row 1. I am considering placement of the checkboxes in either row A or row Y.

The second sheet (Sheet 2) would have the same column headers populated in row 1, with four additional column headers in columns Z through AC. Once all applicable rows have been selected via checkbox, the user would press a VBA-enabled button that would copy only the selected rows into Sheet 2, without producing any row gaps in the data.

The idea behind the macro is to enable users to select a specific set of data from a much larger population, then answer questions / assign attributes about only the selected data set on Sheet 2.

I'm sure I'm omitting relevant details, so please feel free to ask questions to fill in the blanks. THANK YOU!!!

 

[davesexcel]

You can use this code to add forms checkbox to column A, it will then use column Y as the Linked cell.

Sub AddCkBxs()
    Dim sh As Worksheet
    Set sh = ActiveSheet
    Dim rng As Range, c As Range
    Dim CkBx As CheckBox
    With sh
        Set rng = .Range("A2:A" & .Cells(.Rows.Count, "B").End(xlUp).Row)
        For Each c In rng.Cells
            With c
                Set CkBx = sh.CheckBoxes.Add(Left:=.Left, Top:=.Top, Width:=.Width, Height:=.Height)
                With CkBx
                    .Caption = ""
                    .LinkedCell = "$Y$" & c.Row
                End With
            End With
        Next

    End With
    
End Sub

Then this code will loop through column Y and check for True, if true is found it will copy B:x to Sheet 2

Sub MoveToSht2()
    Dim sh As Worksheet, ws As Worksheet
    Dim rng As Range, c As Range

    Set sh = ActiveSheet
    Set ws = Sheets("Sheet2")

    With sh
        Set rng = .Range("Y2:Y" & .Cells(.Rows.Count, "Y").End(xlUp).Row)
        For Each c In rng.Cells
            If c = True Then
                .Range(.Cells(c.Row, "B"), .Cells(c.Row, "X")).Copy _
        ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(1)
            End If
        Next c
    End With

End Sub

Added code

Delete ChkBoxes

Sub DeleteCkbxs()
    ActiveSheet.CheckBoxes.Delete
End Sub

Clear checkbxs

Sub SetCkBxToBlnk()
    Dim sh As Worksheet
    Set sh = ActiveSheet
    With sh
        .Range("Y2:Y" & .Cells(.Rows.Count, "Y").End(xlUp).Row) = ""
    End With
End Sub