Moving Completed Task onto separate excel sheet?

[Lucy_Wood]

I am creating a to-do list for my job. I have two sheets

'Open Tasks' and 'Compleated Tasks'

I'd like the rows with tasks once marked 'Compleated' on Open Tasks, to move automatically to the Compleated Tasks tab.

From what I've read on other threads, I think this requires VBA and as I have absolutely no knowledge of VBA, I was wondering if someone could help me with this?

TIA

 

[My Aswer Is This]

I need some specific details.
In what column will we find the word Completed

 

[Lucy_Wood]

I need some specific details.
In what column will we find the word Completed

Column E

 

[My Aswer Is This]

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 Open Tasks
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

When you enter Completed in any cell in column E the script will run
You said move so this script copies the row to other sheet.
You did not say delete row from existing sheet. If you want that you will have to say so.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 2/21/2020 9:01:00 AM EST
If Target.Column = 5 Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
Dim SheetName As String
SheetName = "Completed Task"
Dim SearchValue As String
SearchValue = "Completed"
If Target.Value = SearchValue Then
Lastrow = Sheets(SheetName).Cells(Rows.Count, 5).End(xlUp).Row + 1
Rows(Target.Row).Copy Sheets(SheetName).Rows(Lastrow)
End If
End If
End Sub

 

[Lucy_Wood]

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 Open Tasks
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

When you enter Completed in any cell in column E the script will run
You said move so this script copies the row to other sheet.
You did not say delete row from existing sheet. If you want that you will have to say so.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 2/21/2020 9:01:00 AM EST
If Target.Column = 5 Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
Dim SheetName As String
SheetName = "Completed Task"
Dim SearchValue As String
SearchValue = "Completed"
If Target.Value = SearchValue Then
Lastrow = Sheets(SheetName).Cells(Rows.Count, 5).End(xlUp).Row + 1
Rows(Target.Row).Copy Sheets(SheetName).Rows(Lastrow)
End If
End If
End Sub

Thanks for this, as I said I am total dumb when it comes to VBA - Do I need to confirm anything once pasted into the VBA edit window to get it to work. if so how??

 

[My Aswer Is This]

You paste the code in the sheet as described above.
The workbook must be macro enabled. Do you know what that means.
Then when you enter Completed in Column E the script will run.
But the word must be Completed I noticed in several places in your post you said
Completed and some places you said Compleated
Those are not the same words

 

[Lucy_Wood]

I've double checked everything Macros is enabled, when I run the VBA shows an error I clicked Debug and it showed this in the VBA Edit?

 

[My Aswer Is This]

Do you have a sheet named "Completed Task"

You will see in the script:
SheetName = "Completed Task"
If that is not the sheet name you want modify that part of the script.

 

[Lucy_Wood]

Do you have a sheet named "Completed Task"

You will see in the script:
SheetName = "Completed Task"
If that is not the sheet name you want modify that part of the script.

I do

 

[My Aswer Is This]

I'm not sure what to say.
Those names must be exact.

I test all my scripts.