If true move to new worksheet from a list

[Brothwood]

Hi all,

I was wondering if there was a way to do this without using a macro, im writing this for use within my company and most employees arent tech savvy.

what i have is a list of faults and actions (columns b & c), column A is a checkbox. all i want is that if the box is checked, it will copy that row to sheet 2 in columns A and B. the problem is i have a list of ~100 rows and sheet two will only have a few of the faults each time they use this document.

Is there a way to do this that can be done simply so that all i have to do check the box and its will instantly appear on sheet 2?

BR
Brothwood

 

[Fishboy]

Hi all,

I was wondering if there was a way to do this without using a macro, im writing this for use within my company and most employees arent tech savvy.

what i have is a list of faults and actions (columns b & c), column A is a checkbox. all i want is that if the box is checked, it will copy that row to sheet 2 in columns A and B. the problem is i have a list of ~100 rows and sheet two will only have a few of the faults each time they use this document.

Is there a way to do this that can be done simply so that all i have to do check the box and its will instantly appear on sheet 2?

BR
Brothwood

Hi Brothwood,

It is not possible to copy data with formulas. You would need some VBA code of some description to achieve this I'm afraid.

 

[Brothwood]

Hi Brothwood,

It is not possible to copy data with formulas. You would need some VBA code of some description to achieve this I'm afraid.

Thats a shame, i didnt think there was but thought id ask.

Is there a simple VBA i can use that i can attach to a button so they can check the boxes they need, then click an "execute" button to get the data?

 

[Fishboy]

Thats a shame, i didnt think there was but thought id ask.

Is there a simple VBA i can use that i can attach to a button so they can check the boxes they need, then click an "execute" button to get the data?

You could try out the following in a COPY of your workbook. This code assumes that there is at least a header value already in A1 of sheet 2:

Sub Test()
' Defines variables
Dim chk As CheckBox, Cell As Range
    ' Sets LastRow as the first blank row on sheet 2 based on column A (requires headers already in place)
    LastRow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
        ' For each checkbox on the active sheet
        For Each chk In ActiveSheet.CheckBoxes
            ' If the checkbox is ticked then...
            If chk.Value = Checked Then
                ' Set Cell as the cell containing the checkbox
                Set Cell = chk.TopLeftCell
                    ' Copy the entire Cell row to the first blank row of sheet 2
                    Cell.EntireRow.Copy Sheets("Sheet2").Range("A" & LastRow)
                        ' Increase LastRow by 1 to account for the new data
                        LastRow = LastRow + 1
            End If
        ' Check the next checkbox
        Next chk
End Sub

 

[Brothwood]

You could try out the following in a COPY of your workbook. This code assumes that there is at least a header value already in A1 of sheet 2:

Sub Test()
' Defines variables
Dim chk As CheckBox, Cell As Range
    ' Sets LastRow as the first blank row on sheet 2 based on column A (requires headers already in place)
    LastRow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
        ' For each checkbox on the active sheet
        For Each chk In ActiveSheet.CheckBoxes
            ' If the checkbox is ticked then...
            If chk.Value = Checked Then
                ' Set Cell as the cell containing the checkbox
                Set Cell = chk.TopLeftCell
                    ' Copy the entire Cell row to the first blank row of sheet 2
                    Cell.EntireRow.Copy Sheets("Sheet2").Range("A" & LastRow)
                        ' Increase LastRow by 1 to account for the new data
                        LastRow = LastRow + 1
            End If
        ' Check the next checkbox
        Next chk
End Sub

That vba is also copies the check boxes which i dont want, i only want the info from columns b and c. is there a way to just copy that across?

br
brothwood

 

[Fishboy]

That vba is also copies the check boxes which i dont want, i only want the info from columns b and c. is there a way to just copy that across?

br
brothwood

Probably something like this:

Sub Test()
' Defines variables
Dim chk As CheckBox, Cell As Range
   ' Sets LastRow as the first blank row on sheet 2 based on column A (requires headers already in place)
    LastRow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
       ' For each checkbox on the active sheet
        For Each chk In ActiveSheet.CheckBoxes
           ' If the checkbox is ticked then...
            If chk.Value = Checked Then
               ' Set Cell as the cell containing the checkbox
                Set Cell = chk.TopLeftCell
                   ' Copy B and C of the Cell row to the first blank row of sheet 2
                    Range("B" & Cell.Row, "C" & Cell.Row).Copy Sheets("Sheet2").Range("A" & LastRow)
                       ' Increase LastRow by 1 to account for the new data
                        LastRow = LastRow + 1
            End If
       ' Check the next checkbox
        Next chk
End Sub