Complex Copy Paste Delete With Various Rules

Hi,

I wondered if anyone could help with the attached. I have attached the sheet to make this clearer.

Example 1: Prod No 2014109 Has Run Out In Sub Location 3 (104B)
Define the Prod No - 2014109
Define The Location - LOCATION1
Define the Values in Sub Loc. 1 / Sub Loc. 2 / Sub Loc. 3 & Sub Loc 4
If the Value Of Sub Loc. 1 / Sub Loc. 2 / Sub Loc. 3 or Sub Loc. 4 = 104B Go to that cell and delete the cell contents
Go to the Sheet Location1
Search Column A for the Sub Loc 2 (104A)
When it is found Delete the cell contents in the cell to the right of it (201409)
That deals with running out of a product in one location.

Example 2: I also need to allow for running out of the products in all locations
After running the above Macro
Come Back to this sheet and Check the Values in Sub Loc. 1 / Sub Loc. 2 / Sub Loc. 3 & Sub Loc 4
If they are all empty then Add a row in row 500 to maintain worksheet size
Then copy the entire row on this sheet to the next blank row on the Out Of Stock Sheet as Values
Then delete the entire row on this sheet