VBA Multiple Check box copy range to 'archive tab'

test.xlsx

Greetings. I have an Excel table with about 4 or 5 columns and I inserted checkboxes in each row. I wanted to program a button to copy and paste the selected row the the arcive table if the checkbox was checked. I tried to attach the file. If anyone could help that would be great. I only can think of the old fashioned way of program an if statement for every single combobox, which clearly is not ideal.

Hopefully the attachment went through.

I definitely think the layout of you sheet is a horrible way of doing it. With that being said, here is the code that does what you want. IMPORTANT NOTES: 1. Your archive sheet is flawed and as such it thinks that there is data in row 100. So when you run the code it will put the rows with the checked boxes below row 100. If you delete rows 3 to 100 it puts it at the top like desired. 2. This code will only allow 100 checkboxes in the data sheet. I didn't have the time to code for more than that.

Sub Transfer_Rows()
Dim ws1 As Worksheet:   Set ws1 = Sheets("Data")
Dim ws2 As Worksheet:   Set ws2 = Sheets("Archive")
Dim shp As Shape
Dim iBox As Integer

For Each shp In ws1.Shapes
    If IsNumeric(Right(shp.Name, 1)) = True And shp.Type = msoFormControl Then
        If Not InStr(1, shp.Name, "Check Box") = 0 Then
            iBox = Trim(Right(shp.Name, 2))
            If iBox = 0 Then iBox = 100  'this takes care of number 100.  we could code for more than 100 but its adds more depth than I want to put in.
            If ws1.Shapes("Check Box " & iBox).OLEFormat.Object.Value = 1 Then
                ws1.Range("A" & iBox + 2).EntireRow.Copy Destination:=ws2.Range("B" & Rows.Count).End(xlUp).Offset(1, -1)
            End If
        End If
    End If
Next shp

End Sub

Thanks for the reply. I'm open to any other ideas to automatically copy a selected row to an archive tab?

I think I would have a "drop down" box (with archive and not archived as options) in column B of the Data sheet. When you are ready to have a row go to archive you simply select "archive". On the back end you would code a worksheet_change event to automatically move that row once "archive" is selected.