Filter out entire row from one sheet to another

[LBroom]

Hi! I am an excel baby and know the basics of excel. I know how to pull up VBA and add modules but not much more. I'm really good at copy/paste

I'm not sure if I need a formula or code, but here's what I am trying to do.

I want to remove entire rows from columns A-N from worksheet "Service Repairs" to worksheet "Completed Repairs" in the same workbook. The last column (N) will have the filter on it so that when the completed date is entered I can push the filter button and it will transfer that entire row to the next available row on the Completed sheet. I'd also like for rows to move up after the data is transferred on the service repairs sheet.

Any help is most appreciated! Thank you!
~Leah

 

[Dryver14]

so there is nothing in column N until you put a date in it?

 

[LBroom]

so there is nothing in column N until you put a date in it?

Yes, exactly.

 

[My Aswer Is This]

Not sure how to do it the way you mentioned.
But you can do it this way.
Double click on column (N) of the row you want copied to sheet named "Completed Repairs"
And that row will be copied to sheet named "Completed Repairs" and deleted from sheet named "Service Repairs"

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

Put this script in the sheet named:"Service Repairs"

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Target.Column = 14 Then
Dim ans As Long
Dim Lastrow As Long
ans = Target.Row
Lastrow = Sheets("Completed Repairs").Cells(Rows.Count, "N").End(xlUp).Row + 1
Rows(ans).Copy Sheets("Completed Repairs").Rows(Lastrow)
Rows(ans).Delete
End If
End Sub

 

[Dryver14]

Cheers My Answr is this....That's a good learn for me, I was trying to think of the best way to activate the code...I'll put that in the memory bank

 

[LBroom]

That worked perfectly to remove the data from the Service repairs sheet and I am able to bring it back with that filter if I need it, but is there a way to transfer that filtered data to another sheet i.e. Completed Repairs?

THANK YOU SO MUCH!

 

[My Aswer Is This]

Cheers My Answr is this....That's a good learn for me, I was trying to think of the best way to activate the code...I'll put that in the memory bank

I could have suggested this script:
Notice line one of the code is not the same
It activates when you enter any value in column "N"
But I'm always concerned if a user makes a mistake and enters something in column "N" by mistake the script automatically runs.
Double clicking a cell is hard to do accidently.

Private Sub Worksheet_Change(ByVal Target As Range)
Cancel = True
If Target.Column = 14 Then
Dim ans As Long
Dim Lastrow As Long
ans = Target.Row
Lastrow = Sheets("Completed Repairs").Cells(Rows.Count, "N").End(xlUp).Row + 1
Rows(ans).Copy Sheets("Completed Repairs").Rows(Lastrow)
Rows(ans).Delete
End If
End Sub

 

[Dryver14]

I tried that but when I clicked on column N to insert the date the row disappeared

 

[My Aswer Is This]

That worked perfectly to remove the data from the Service repairs sheet and I am able to bring it back with that filter if I need it, but is there a way to transfer that filtered data to another sheet i.e. Completed Repairs?

THANK YOU SO MUCH!

The script I provided does both. Copies row to Completed Repairs and deletes from "Service Repairs"

Not sure what your doing with your filter.
My script does not require a filter.
But my script does not provide a way to bring it back later.
You never said you wanted that option and that gets real complicated.

 

[LBroom]

The script I provided does both. Copies row to Completed Repairs and deletes from "Service Repairs"

Not sure what your doing with your filter.
My script does not require a filter.
But my script does not provide a way to bring it back later.
You never said you wanted that option and that gets real complicated.

Hahaha leave it to me to complicate matters. Reading the other replies I did not realize there was a double click for that column that would then transfer it, I did that and it work perfectly! Thank you so much!

But so you know what happened on my end was Column N somehow now had an advanced filter on it, when you clicked that it gave you options sort it into year and month and then blanks. If you clicked the blanks it removed it entirely from the page but did not transfer it. If you selected that filter again and clicked on the year/month it restored it to the page. I'm guessing because I already had filters on those columns it was able to do that?