Checkbox to copy row to new sheet

[ADAMC]

Hi all,
Thanks in advance for any help.
I have 4 sheets
Sheet 1
Sheet 2
Sheet 3
"Checkout"

What i would like:
Rows of data on Sheets 1/2/3 with a checkbox in column A of each row
I check a couple of checkboxes on sheet 1 some on sheet 2 and some on sheet 3
where i put a tick in checkbox they have copied across to next available row in sheet "checkout"

If i tick a checkbox then untick it (maybe changed mind) copied row is removed from checkout sheet.
Any help greatly appreciated i know the basics but not enough to get me going.
Im assuming it will take VBA but i could be wrong (an on this site i usally am)

THANKS!

 

[MickG]

Hi, That right, you could colour the font the same colour as the cell if you don't what to see the value, and place the checkbox over the cell.
I think the code copies over the first 10 cells in the row. If the code seems of any use I could modify it to be more usefull.
Regards Mick

 

[ADAMC]

Hi MickG

Thanks for all your help, does almost what i like.....

I set up some test data just 5 rows and assigned the macro to the checkboxes
If i check then uncheck a checkbox it removes the line from the checkout sheet but leaves a copy of the checkbox behind.
Then if i check a different checkbox it inserts it on the same line and copies another checkbox over.

Is there anyway to remove the checkbox either altogther from the checkout sheet, or make it also delete if the checkboxes are unticked on sheets 1/2 or 3.

Hope that makes sense!

Thanks!!!

 

[MickG]

Hi, The original code copied over 10 cells from a row an included columm "A". This code copies 10 cells across from column "B" (Alter length to suit "See code rem").
If you place this code in a "Module" and then place the Second bit of code in each Check Box Macro (Right click Check Box , select "Assign Macro") the code will run when you click "True or False" in any checkbox.
This code (same as previous code) initially clears all items in sheet "CheckBox" except row (1) then copies over from sheets 1 to 3 any rows whose check box is Ticked (True).
Hope that is what you want.
NB:- from your Pre/pre post I should have said When you right Click the "CheckBox" and select Format Control the Linked cell should be the Address in column "A" that the Check Box refers to. Like "A10" for row 10.. Sorry if I'm sounding obvious.
When you say it copies over the check Box I assume you mean the "True or False" Bit ???.

[COLOR=navy]Sub[/COLOR] Chbx()
[COLOR=navy]Dim[/COLOR] shp [COLOR=navy]As[/COLOR] Shape, ws [COLOR=navy]As[/COLOR] Worksheet, msg [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] cop
c = 1
[COLOR=navy]With[/COLOR] Sheets("Checkout")
cop = .Rows(1).Value
.Cells.ClearContents
.Rows(1).Value = cop
[COLOR=navy]End[/COLOR] With
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] ws [COLOR=navy]In[/COLOR] ActiveWorkbook.Worksheets
[COLOR=navy]If[/COLOR] Not ws.Name = "Checkout" [COLOR=navy]Then[/COLOR]
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] shp [COLOR=navy]In[/COLOR] ws.Shapes
        [COLOR=navy]If[/COLOR] shp.Type = msoFormControl [COLOR=navy]Then[/COLOR]
            [COLOR=navy]If[/COLOR] shp.FormControlType = xlCheckBox [COLOR=navy]Then[/COLOR]
                [COLOR=navy]If[/COLOR] shp.ControlFormat.Value = 1 [COLOR=navy]Then[/COLOR]
                    c = c + 1
     ' Alter "Resize" number below for the number of cells across, to copy over.               
ws.Range(shp.ControlFormat.LinkedCell).Offset(, 1).Resize(, 10).Copy Sheets("Checkout").Cells(c, 1)
                [COLOR=navy]End[/COLOR] If
            [COLOR=navy]End[/COLOR] If
        [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] shp
[COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] ws
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

call Chbx

Regards Mick

 

[ADAMC]

Hi MickG

Yes it was copying over the true or false but it doesnt seem to be now.

Ive just tested it on a blank sheet and it works amazingly well. Tomorrow ill test it on my actual sheet where my catalogue is held.

Thanks very much for all your help ill post back and let you know when it works OK.

Many, many thanks!!

 

[Eric Young]

Hi, I know this post is old. But is there a way to have last code MickG put up copy and paste an array like "range1" instead of a row?

 

[Eric Young]

Correction to request, I figured out how to get the array but I can't figure out how to augment the code to select the next empty cell in column A. It keep over writing the previous array except for the first line of the array.

 

[lady_alina]

Need help with check box to copy data in different sheet in same work book

I have data in sheet1 and check box in column A against each line. I want to copy the data in next sheet when check box is checked, however the code that I have works only for each check box and it is a very tideous procedure and it doesn't really help much.

What I want is a code that will work for all check boxes (count of check box will be around 5000). The code should identify the available empty row and copy the checked data at once in the next available row in the other sheet. Also headers are not copied so want a solution for that as well. If the checked checkbox is unchecked then the data should be remove from that sheet and row should be deleted or the lines below that should move up. Hope my question makes sense.

Private Sub CheckBox1_Click() With Sheets("Checkout")
ckrownum = .Cells(Rows.Count, "B").End(xlUp).Row
If CheckBox1 Then
Range("B1:D1").copy
.Range("B" & ckrownum).PasteSpecial
Else
For x = 1 To ckrownum
If .Cells(ckrownum, 2) = Cells(1, 2) Then
.Rows(ckrownum).Delete
Exit For
End If
Next
End If
End With
End Sub