Copy, paste special, all checked rows in range

[pann8888]

I have a sheet (Data_View) with 300 rows of information. In column A, there is a checkbox for each row (checked box = TRUE). If a given row’s checkbox is clicked, I need that row’s information to be copied and pasted (values only) to a separate worksheet (Selected_Items). I can write code to do this for a specific row, but I don’t know how to write a loop that will check all 300 rows for the condition, copy only the checked rows, then paste the copied data into successive rows in Selected_Items sheet.

I’ve attempted to modify code from other posts related to this topic, but I'm a VBA novice and I haven’t gotten it right yet. My efforts thus far are available for advice (and mockery) below. Any help would be greatly appreciated.

Dim rng As Range
Dim row As Range
Dim cell As Range
Dim nextrow as Long
Nextrow = Sheets.(“Selected_Items”).cells(Rows.Count, “A”).End(xlUp).Row +1
Set rng = Range("A2:BD2")
Sheets(“Data_View”).Select
 For Each row In rng.Rows
   For Each cell in row.Cells
If Range(“A”).value = True then row.copy
Sheets("Selected_Items").Cells(nextrow, “A”).PasteSpecial Paste:=xlValues
Next cell
Next row
End if
End sub

</PRE>

 

[Firefly2012]

What kind of check box do you have? Is it from the Formas toolbar or ActiveX from the Controls Toolbox? When you say they are in column A, are they wholly contained by the Row in which they sit (ie so the checkbox for A2 would not cross the top or bottom cell boundary of A2)?

 

[pann8888]

Exactly- each checkbox is wholly contained by the row in which it sits. If a box is checked, it returns "TRUE" in its cell (e.g., A2). I used the following code to add the checkboxes.

</SPAN></SPAN>Sub AddCheckBoxes()
On Error Resume Next
Dim c As Range, myRange As Range
Set myRange = Selection
For Each c In myRange.Cells
ActiveSheet.CheckBoxes.Add(c.Center, c.Center, c.Width, c.Height).Select
With Selection
.LinkedCell = c.Address
.Characters.Text = ""
.Name = c.Address
End With
c.Select
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & c.Address & "=TRUE"
.FormatConditions(1).Font.ColorIndex = 6
.FormatConditions(1).Interior.ColorIndex = 6
.Font.ColorIndex = 2
End With
Next
myRange.Select
End Sub

</SPAN>

 

[Firefly2012]

Ah I see - well, you've got most of the problems sorted by making it place True in the cells. Try this:

Sub Copy_Data()
Dim cell As Range

With Sheets("Data_View")   'amend as required
  For Each cell In .Range("A2:A" & .Cells(.Rows.Count,"A").End(xlUp).Row)
    If Typename(cell.Value) = "Boolean" Then
          If cell.Value = True Then 
                With Sheets("Selected_Items")
                    cell.EntireRow.Copy
                    .Cells(.Rows.Count,"A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
                End With
          End if
       End If
   Next cell
End With
End Sub

 

[pann8888]

Worked like a charm - thanks, Firefly!

 

[Firefly2012]

You're welcome