I have a VBA - Excel2007 code which copies a range from one sheet to several other sheets based on the value of a cell. The workbook is attached. What this code does is looks for value in D5 in "MasterList" and copies a range from a sheet named "Ranges" to appropriate destinations in other sheets as mentioned in the sheet named "List". Under col F5 in MasterList!, is a formula which assigns a unique ID to the selected combination from dropdown lists in Mastersheet!. The selection_change macro is in the view code of MasterList sheet.

When there is a change in selection of E5, the copy which was done before the change is now cleared from its destination and instead a new copy is effected based on the new selection made in E5. The clearing of the OLd copy takes place from bottom of the populated sheet. Which means the last copied range is the one which is deleted.
This is where my code is doing wrong. What I actually want is, the same range which was copied due a selection must be cleared and not the last copied range. Please note that, if this helps, the value under col. F - which is Tag No.in MasterList! is unique to every selection that is made. This fact could be useful in co-relating what caused the copy and what should be cleared.

I woould like to get this code modified to suit the above requirement.

Second, As a sheet gets populated due to subsequent copy of the ranges, I would like to make sure that the ranges are not split in successive pages and also make sure that the copy into the next sheet takes place after allowing 5 rows for the header. This is indicated in the sheet by name 'ControlValve".

To try out various options please copy the rows into as many new rows as needed in the MasterList!. I have indicated 4 rows (Row 5 : Row 8) in the masterlist! which can be further -drag-pulled down.