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

Xc3l3r4t0r

New Member
Joined
Apr 8, 2015
Messages
4
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!!!
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
1,025
You can use this code to add forms checkbox to column A, it will then use column Y as the Linked cell.

VBA Code:
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

Code:
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

Code:
Sub DeleteCkbxs()
    ActiveSheet.CheckBoxes.Delete
End Sub

Clear checkbxs

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,978
Messages
5,545,308
Members
410,675
Latest member
DLongmountain
Top