PDA

View Full Version : Checkbox - Automatic Copying to Different Sheet



Clintonlion
05-22-2013, 04:35 AM
Hi there Guys

I have a small problem which i can't figure out. I have 3 Sheets in Excel and the "Main" sheet is where all the information will be entered. I have 2 Check boxes near the end of the row for different Options. One is Printed and the other Sent.

Now what i want to happen is when the check box for Printed is checked is copy the whole line into the "Printed" Sheet and if Unchecked remove it. I have managed to program that but when i get the 2nd checked for the next customer it overwrites the 1st customer in printed. It also overwrites the headings. The same will happen with Sent.

The code i am using was found on another forum and adpated it.

Private Sub CheckBox1_Click()
With Sheets("Printed")
ckrownum = .Cells(Rows.Count, "B").End(xlUp).Row
If CheckBox1 Then
Range("A2").Resize(, 15).Copy '(Obviously you'll need to adjust this range to your needs
.Range("A" & 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
Private Sub CheckBox11_Click()
With Sheets("Printed")
ckrownum = .Cells(Rows.Count, "B").End(xlUp).Row
If CheckBox1 Then
Range("A3").Resize(, 15).Copy '(Obviously you'll need to adjust this range to your needs
.Range("A" & ckrownum).PasteSpecial
Else
For x = 1 To ckrownum
If .Cells(ckrownum, 3) = Cells(3, 4) Then
.Rows(ckrownum).Delete
Exit For
End If
Next
End If
End With
End Sub


Thanks in Advance

SamT
05-22-2013, 08:22 AM
Lion,
I am not sure enough about what you are trying to accomplish to write any code for you, but I did analyze what code you have.

Here is what you code is really doing

'CheckBox "Print" was Clicked, True or False, we'll find out.
'On sheet(Printed", get Last Row with content in Column "B"

'If CheckBox "Print" was clicked "True" Then
'Copy This Sheet Range "A2" to "O2"
'OverWrite Last Row with content in Column "B" on Sheet "Printed"

'If Checkbox "Print" was clicked "False" Then
'Iterate the number of times there are Rows on sheet "Printed" with Content in Column "B"
'If Range("B" "LastRow") = "B1" then
'Delete whatever Row number was the last one several times.
'Actually, this line in this loop means deleting a number of rows starting at the "lastrow" 'equal to the number of the "last" row.
'Except, Really, Exit the loop after deleting only one Row.
'Loop

'CheckBox "Sent" was Clicked, True or False, who knows?
'On sheet(Printed", get Last Row with content in Column "B"

'If CheckBox "Print" is Checked Then
'Copy this sheet, Range "A3" to "O3"
'Overwrite Last used Row on Sheet "Printed"

'If Checkbox "Print" is not checked, Then
'Iterate the number of times there are Rows on sheet "Printed" with Content in Column "B"
'If, Range("C" LastRow) = Range "D3" then
'Delete whatever Row number was the last one several times.
'Actually, this line in this loop means deleting a number of rows starting at the "lastRow", Except, Really, Exit the loop after deleting only one Row.
'Loop


Here is your code with the above comments included. Note that comments refer to the following code lines.

On Clicking CheckBox "Print"
'CheckBox "Print" was Clicked, True or False, we'll find out.
Private Sub CheckBox1_Click()
With Sheets("Printed")
'On sheet(Printed", get Last Row with content in Column "B"
ckrownum = .Cells(Rows.Count, "B").End(xlUp).Row
If CheckBox1 Then 'If CheckBox "Print" was clicked "True" Then
'Copy This Sheet Range "A2" to "O2"
Range("A2").Resize(, 15).Copy
'OverWrite Last Row with content in Column "B" on Sheet "Printed"
.Range("A" & ckrownum).PasteSpecial
Else 'If Checkbox "Print" was clicked "False" Then
'Iterate the number of times there are Rows on sheet "Printed" with Content in Column "B"
For x = 1 To ckrownum
'If Range("B" "LastRow") = "B1" then
If .Cells(ckrownum, 2) = Cells(1, 2) Then
'Delete whatever Row number was the last one several times.
'Actually, this line in this loop means deleting a number of rows starting at the "last"
'equal to the numer of the "last" row.
.Rows(ckrownum).Delete
'Except, Really, Exit the loop after deleting only one Row.
Exit For
End If
Next
End If
End With
End Sub

On Clicking CheckBox "Print"
'CheckBox "Sent" was Clicked, True or False, who knows?
Private Sub CheckBox11_Click()
With Sheets("Printed")
'On sheet(Printed", get Last Row with content in Column "B"
ckrownum = .Cells(Rows.Count, "B").End(xlUp).Row
If CheckBox1 Then 'If CheckBox "Print" is Checked Then
'Copy this sheet, Range "A3" to "O3"
Range("A3").Resize(, 15).Copy
'Overwrite Last used Row on Sheet "Printed"
.Range("A" & ckrownum).PasteSpecial
Else 'If Checkbox "Print" is not checked, Then
'Iterate the number of times there are Rows on sheet "Printed" with Content in Column "B"
For x = 1 To ckrownum
'If, on Sheets("Printed"), Range("C" LastRow) = Range "D3" then
If .Cells(ckrownum, 3) = Cells(3, 4) Then
'Delete whatever Row number was the last one several times.
'Actually, this line in this loop means deleting a number of rows starting at the "last"
.Rows(ckrownum).Delete
'Except, Really, exit the loop after deleting only one Row.
Exit For
End If
Next
End If
End With
End Sub

Clintonlion
05-22-2013, 08:43 AM
Hi there SamT

I am not a coder, but thanks for the explation.

Okay i will try and explain what i am trying to do.

We have a list of about 100 customers and want to see them documentation. So the main sheet is where all the input will go. Then if we have printed the documentation then we will check Printed and it should copy that customer information into the Printed Tab. Then when we send it and click send then it will copy into the sent Tab. This is for checking purposes. Hope that makes sense.

I thought that the code would work but seems to be somewhat flawed.

Any more help would be greatly appricated.

Thanks

Clint

SamT
05-22-2013, 02:57 PM
Hi there SamT

I am not a coder, but thanks for the explation.

Okay i will try and explain what i am trying to do.

We have a list of about 100 customers and want to see (do you mean "Send"?) them documentation. So the main sheet (Is that where the code goes, too?) is where all the input will go. Then if we have printed the documentation (What sheet are you printing?) then we will check Printed and it should copy that customer information into the Printed Tab. Then when we send it (Hunh? What? How?) and click send then it will copy (From Where?) into the sent Tab. This is for checking purposes. Hope that makes sense.

Ok, so you have a customer "list thingie," (Workbook, Paper, I-Pad, I dunno,) that you're looking at and entering some stuff into sheet("Main"), Columns "A" to "O", on row 2 or 3, (I can't tell from your code or description.)

After you print some stuff from your "list thingie," you want to paste the information from Sheet("Main") to Row 2 on the sheet ("Printed").

After you send the Printed Form to the customer, you want to remove the the line from Sheet("Printed") and put it on sheet("Sent").

Then, you will look at another customer on your "List Thingie" and replace the info on Sheet("Main") and start over with the Print and Send stuff, always replacing the old info with the new info on all three sheets.

I am spending several hours trying to understand what you want in order to help you. :banghead:

Why don't you take the same time to study and understand the code explanation I gave you above and then use the same step by step technique to tell me what you want. I am doing this for free and your company is going to save a lot of money through my efforts.:beerchug:

Clintonlion
05-23-2013, 04:50 AM
Ok, so you have a customer "list thingie," (Workbook, Paper, I-Pad, I dunno,) that you're looking at and entering some stuff into sheet("Main"), Columns "A" to "O", on row 2 or 3, (I can't tell from your code or description.)

After you print some stuff from your "list thingie," you want to paste the information from Sheet("Main") to Row 2 on the sheet ("Printed").

After you send the Printed Form to the customer, you want to remove the the line from Sheet("Printed") and put it on sheet("Sent").

Then, you will look at another customer on your "List Thingie" and replace the info on Sheet("Main") and start over with the Print and Send stuff, always replacing the old info with the new info on all three sheets.

I am spending several hours trying to understand what you want in order to help you. :banghead:

Why don't you take the same time to study and understand the code explanation I gave you above and then use the same step by step technique to tell me what you want. I am doing this for free and your company is going to save a lot of money through my efforts.:beerchug:

Hi SamT

I understand that you are doing this for free and really appreciateit. Let see if I can put this in a step by step technique so you understand.

I have 3 Sheets in a workbook

· Main (Tab 1)
· Printed (Tab 2)
· Sent (Tab 3)

All customers are in the main sheet, each customer on oneline with address information etc etc. The check boxes are in column L & M.L is the “Printed” check box and M is the “Sent” Check Box.

Now once documentation for a customer is Printed, I wantto check the “Printed” check box and the information on the entire line eventhe checkboxes copied over not moved into the “Printed” Worksheet (Tab 2). If Ideselect it for some reason I want it to be removed off the “Printed”worksheet.

Now once documentation for a customer is Sent out to themand I check the “Sent” check box I would like the information on the entireline even the checkboxes copied over not moved into the “Sent” Worksheet (Tab 2). If I deselect it forsome reason I want it to be removed off the “Sent” worksheet.

Hope this all makes sense and are able to help me out.

Clinton

P.S. Workbook is attached.

Clintonlion
06-07-2013, 02:31 AM
Can anyone help me on this!!

Thanks in advance