VBA Question: Moving data from 5 Sheets to another sheet..If data is deleted from 5 sheets move data up on other sheet or if nothing from 5 sheets mov

[menor59]

Hello all,

And thank you for taking the time in reading this.

Ill Try to make this as painless as possible.

5 Sheets (named CO 1, CO 2, CO 3, CO 4 and CO 5) Each sheet is layed out the same...
1 Sheet is where the Data will be placed (Large_SOV).
If any data is present in any rows on the 5 sheets (C13:F62) then
Data should be copied from the 5 sheets that are in C13:F62 and will be "inserted" into the (Large_SOV) B20:E*) I say * because in theory the said sheet if you total the rows from the 5 sheets could be in the range of 280 Rows (56 Rows from the sheets times 5 sheets).

Heres the Wish if possible.

See sample 1 attached Pic
No data on Any of the 5 sheets

This is the Default View of the Large_SOV Sheet F20:K20 Contain Formulas that are $'ed
If any data is present in any of the 5 CO Sheet ranges specified...the Large_SOV should look like:
See Attached sample 2

Lets say i go back and add data to CO 3, The data from that sheet should be placed in its proper order between 2 & 4 moving 4 down to make room for CO 3's data.


Ten if no data is present in any of the CO sheets Return back to sample 1

If needed the the formulas and formats in F20:K20 are

F20 = Currency $ 2 Decimals
G20 =

 =IF(F20=0,"-",($F20/$E20))

H20 = Currency $ 2 decimals
I20 =

 =IF($H20=0,"-",$H20/$E20)

J20 =

 =IF(H20=0,"-",($H20+$F20)/$E20)

k20 =

 =$E20-$F20-$H20

Those values need to populate down also.
Thoughts? Too much?? Sorry in advance.

 

[menor59]

Again Thank you in advance.