If true move to new worksheet from a list

Brothwood

New Member
Joined
Mar 16, 2015
Messages
33
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Fishboy

Well-known Member
Joined
Feb 13, 2015
Messages
4,261
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

New Member
Joined
Mar 16, 2015
Messages
33
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

Well-known Member
Joined
Feb 13, 2015
Messages
4,261
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:

Rich (BB code):
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

New Member
Joined
Mar 16, 2015
Messages
33
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:

Rich (BB code):
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

Well-known Member
Joined
Feb 13, 2015
Messages
4,261
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:

Rich (BB code):
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,682
Messages
5,637,764
Members
416,982
Latest member
lisam77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top