Transfering data from one worksheet to another base on a check box

[RobCTD]

I have two worksheets - Live Projects and Dead Projects - each of which has six columns. In the sixth column (G) I have a check box. When this is checked (ticked) it denotes that the project in this row is now dead and should move to the Dead Projects sheet and move any remaining Live Projects up to fill the row vacated by the dead project.

I am sure there must be a bit of VBA code and can put in one or other of the tabs but I could really do with some help please.

Many thanks in advance

Rob

 

[My Aswer Is This]

It would be a lot easier if you did not use check box's
I could write you a script where when you double click on a cell in Column F
That row would be copied to another sheet and delete from the sheet you double clicked on.
To use checkboxes means you have to put hundreds of checkboxes on your sheet.
Would not double clicking on the cell not work.
If this would work then try this:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab named: Live Projects
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("F:F")) Is Nothing Then
'Modified  10/10/2018  11:29:37 AM  EDT
Cancel = True
Dim ans As Long
ans = Target.Row
Dim Lastrow As Long
Lastrow = Sheets("Dead Projects").Cells(Rows.Count, "F").End(xlUp).Row + 1
Rows(ans).Copy Sheets("Dead Projects").Rows(Lastrow)
Rows(ans).Delete
End If
End Sub

<strike></strike>

 

[RobCTD]

Dear MAIT

Thanks for the reply. I am very much a part time Excel user, so please forgive my lack of understanding/ignorance/stupidity.

I would prefer to use a check box if at all possible because I already have over 1350 rows in the Live Projects worksheet and have checkboxes already in place which are either checked or unchecked depending on the status of the project (they go back over ten years!)

When I insert another row at the top of the Live Projects sheet (to add a new project) I can simply copy the checkbox from the row below. I only tend to add a few new projects each week no it's not a big chore.

Hope this makes sense and thanks for helping.

Cheers

Rob

 

[My Aswer Is This]

If you already have hundreds of checkboxs and this has been working for you what do you need help with?

Show me the script you are now using

 

[RobCTD]

I don't have a script at all I'm afraid. What I need is something similar to the one you kindly posted earlier except it needs to work with a yes/no condition as defined by the check box in column F rather than the double click option you suggested. Many thanks Rob

 

[My Aswer Is This]

So you have already installed 1300 check boxes. And now want to know how to write a script to do what you want?

You installed all these check boxes manually ??

 

[RobCTD]

I just placed one in the first row and copied and pasted them down the column. Then, as most of the rows are Dead Projects, I put a tick in one and replaced the unticked boxes with ticked one's where appropriate, again by copy and pasting. Have I made big mistake? Thanks Rob

 

[My Aswer Is This]

So your saying you have a checkbox in column G and when you click the checkbox it copies that row to the other sheet and deletes the row on the sheet with the check box.

So if this already works then it must work because you have a script working someplace.
Just clicking on a checkbox with no code will not do that.

Now you said I do not want to use double click because you already had 1300 check box's installed.

If that' the case I would have never installed 1300 Checkboxes till I had this figure out.

I do not know how to do what you want.
But maybe someone else here on the forum will have a answer.

 

[RobCTD]

Sorry, I'm clearly not explaining what I want very well. The check boxes are already there but currently have no effect on anything. There is no script.

I need the script to tell any given row in the Live Projects worksheet to move to the Dead Projects worksheet when the check box in column F is ticked.

I need this to act both on the 1350 rows which already exist as well as on any new rows that I add at the top of the Live Projects sheet when they ultimately become dead.

Sorry if I have been vague and thank you so much for your time and effort so far.

Cheers Rob

 

[My Aswer Is This]

I really do not know how to that.

Maybe someone else here on the forum will have a answer
I will continue to monitor this thread to see what I can learn.