Delete paste-linked row from another sheet after that sheet has been deleted

Hello all! This is one of my last obstacles keeping me from finishing my work on this workbook for now, so any help will be greatly appreciated.

Here is my workbook. Whenever you press Ctrl+Shift+S, a new sheet is created, which is a copy of the Template sheet. Upon creation, Row 3 of this sheet is automatically paste-linked to the next available row in the Summary sheet. However, if you were to delete that new sheet, the corresponding row on the Summary sheet will throw out #REF! errors. I need a way to delete that row once the corresponding sheet is deleted, and shift the cells up.

I was thinking, maybe I could make a loop that scans column A for cell references containing "REF" in them or something. Then, when it finds one, it selects cells A:K of that row, deletes them, and then shifts the cells up. But I'm not sure how to do that.

Thanks in advance!!

Also, here is one section of code where I need this to be done. Whenever the Status of a sheet is set to "Archived" or "Complete," that sheet is automatically moved to another workbook, and the sheet is deleted from this workbook. I have commented out the area of the code where I think this needs to be done, and my ideas for how to do it.

Please Login or Register  to view this content.

Any ideas?