Moving entire row to another sheet when column shows "closed" value

Hi guys,

Have been reading through streams of similar threads on here and have tried a few to see if it can accommodate what I need it to do, but am getting nowhere.

As I am at work, it does not allow me to upload the file due to restrictions on uploading. However, the spreadsheet is fairly simple so should be able to describe it.

The main sheet is called "Investigation Court Apps". An entry is made into each row and the status shows as "Open" in Column A, until a final closure date is input in Column Z, at which point the entry in Column A changes to "Closed". This is achieved with the following IF statement in Column A.
=IF(B3="","",IF(ISNUMBER($Z3),"Closed","Open"))

So that part is all automated. What I need is some VBA coding or macro (might be same thing but completely new to all this) that when the value in Column A changes to "Closed" the whole row moves over to "Sheet3". Once the row has been moved over, I want to ensure there are no blank rows also.

I have tried the following amongst others:

Sub MoveToSheet3()
Dim C1 As Range
Dim RowNum As Integer

For Each C1 In Sheets("Investigations Court Apps").Range("N1:N" & Range("A65536").End(xlUp).Row)
If Cll = 0 Then
RowNum = Sheets("Sheet3").Range("A65536").End(xlUp).Row + 1
Cl.EntireRow.Copy Destination:=Sheets("Sheet3").Cells(RowNum, 1)
Cl.EntireRow.Delete
End If
Next Cl
End Sub

I am unsure what part I need to adjust, and have only adjusted the bits that I think need adjusting so may just need tweaking or maybe wrong altogether.

Also if anyone is able to help, please give dummies instructions on how to apply the code or where to copy to. As I have only recently learnt formulas, so VBA and its function are way over my head. I know to open VBA its Alt+F11 and to close Alt+Q, but thats about it. Just kidding but you know what I mean.

Thank you as always and look forward to trying a few more things.

Thanks and Kindest Regards

Hi OllyXLS,

I tried that and it did nothing? I right clicked the investigations court app sheet, pressed view code and entered the above. I saved and closed and on restart enabled macros but it just did nothing unfortunately.

Am I doing something wrong?

Thanks

Imran

Hi OllyXLS,

Yes Row Z has data validation so it only accepts dates. I tried the coding once again it did nothing unfortunately.

Is there anyway to upload my spreadsheet on here. I have had the upload restriction removed. But when I go to upload via the manage attachments, they simply dont appear after pressing upload.

I think it will make life easier if it was uploaded as don't wish to waste any more of your time on this.

Thanks and Regards

Imran