Copy rows with ticked checkboxes to a new sheet in vba-CodePudding

I tried this code but it seems to exclude the headers and paste the content in the same sheet.

Sub Copy_to_new_sheet()
    Dim Row1 As Long, ChkBx As CheckBox, WS2 As Worksheet
    Set WS2 = Worksheets("Sheet1")
    Row1 = WS2.Range("A" & Rows.Count).End(xlUp).Row
    For Each ChkBx In ActiveSheet.CheckBoxes
        If ChkBx.Value = 1 Then
            Row1 = Row1   1
            WS2.Cells(Row, "A").Resize(, 14) = Range("A" & _
            ChkBx.TopLeftCell.Row).Resize(, 14).Value
        End If
    Next
 End Sub

CodePudding user response：

Your code should look like this

Option Explicit
Sub Copy_to_new_sheet()
    Dim Row1 As Long, ChkBx As CheckBox, WS1 As Worksheet, WS2 As Worksheet
    Set WS1 = Worksheets("Sheet1") 'Source worksheet
    Set WS2 = Worksheets("Sheet2") 'Destination worksheet
    WS1.Rows(1).Copy 'Copy header in row 1
    WS2.Rows(1).PasteSpecial xlPasteValues 'Paste header in destination worksheet
    
    Row1 = WS1.Range("A" & Rows.Count).End(xlUp).Row
    For Each ChkBx In ActiveSheet.CheckBoxes 'The sheet with ckeckboxes must be selected (ActiveSheet)
        If ChkBx.Value = 1 Then
            Row1 = Row1   1
            'Assign value to cells in destination sheet
            WS2.Cells(Row1, "A").Resize(, 14) = Range("A" & _
            ChkBx.TopLeftCell.Row).Resize(, 14).Value
        End If
    Next
End Sub