Cut and Paste Rows to New Worksheet based on date in column - protected worksheet

Hi, am posting to take advantage of the obvious expertise in this forum...ok grovelling over.

I am trying to create a To-Do list, which will automatically (or at the press of a button) cut and paste a range of data from the active worksheet to another named worksheet in the workbook, based on a date being entered in Column G of the active worksheet. I would like all but the one named ("Completed") worksheet to be protected so that I am only tabbing through certain columns.

I've got some code in the workbook module, which was working up to a point, but seems not to now. Any help would be gratefully received. I'd also like the macro to run either as soon as the date is entered in the column or by clicking a button on the toolbar. I've attached what I've done so far.

Cheers

Hi

How about a sheet event macro that will take the data from the current sheet and move to Completed when a date is entered in column G.

[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 And Target.Column = 7 Then
Target.EntireRow.Copy Destination:= _
Sheets("completed").Cells(Rows.Count, 2).End(xlUp).Offset(1, -1)
Target.EntireRow.Delete
End If
End Sub
[/vba]
Hope that points you in the right direction.


Tony

Hi Tony,
Minor point, but why not Cut instead of Copy/Delete?

MD

Overkill to some extent. If you cut, have a problem (say can't paste for some reason), you could lose the original data. If you copy, but can't paste, the original data is intact. Bit of an attempt to emulate a database rollback scenario.

Also helps with testing if you have a problem and have to debug.


Tony

that macro works but any newly completed tasks just overwrite the first entry on the completed worksheet. Any code to make sure that the next completed task gets written on the first available empty row. Also, it won't work if the task worksheet is protected. What code do I need to be able to unprotect the sheet to remove the completed row and then re-protect the sheet?

TIA
Nicki

Hi Nicki
Try
[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect
If Target.Cells.Count = 1 And Target.Column = 7 Then
Target.EntireRow.Copy Destination:= _
Sheets("completed").Cells(Rows.Count, 2).End(xlUp).Offset(1, -1)
Target.EntireRow.Delete
End If
ActiveSheet.Protect
End Sub

[/VBA]
Regarding writing to the next line, check that there is always data in column B, if not, change the figures for a column which always has data. Note that 2 changes as are required
For column D
Sheets("completed").Cells(Rows.Count, 4).End(xlUp).Offset(1, -3)

that works perfectly, thank you so much :)