Inserting a row that carries formulas

Lets say I have cells A2:A6 containing formulas that depend on an entry into A1. I need a macro that, once a value is entered into A1, will insert a new row underneath A and carry with it the formulas into B2:B6 that now depend on an entry into B1. Copy and past will not work because D1:D6 MUST drop so that it does not end up getting overwritten. I have tried numerous things but none have worked. There are other functions in the spreadsheet but I think if I can figure out a solution for the above, I can make it all work together.

I tried the macro you gave me but could not get it to do anything at all. Blame my ignorance in VB.
Here is the closest thing I have found but it only inserts a row above the defined range and copies/pastes the formulas from the above row. I need the formulas to change since they are now on the next row.

Please Login or Register  to view this content.

Basically I need the list to get longer without disturbing anything below the inserted row while maintaining the correct formulas. On top of that, I would like this to be executed without clicking a button if a certain cells value change to anything but zero.

I think I attached an example, hope it works. When ticker symbols are entered into B5 or B12, the stock quotes from msn are updated in the tables starting in R20 provided by the msn moneycentral investor stock quotes connection. The formulas in B7:O11 and B14:O17 pull information from the msn tables. The result is an update of all tables when adding or deleting a ticker symbol in B5 or B12. The problem is that if you were to add 4 more ticker symbols into B5, only 3 would appear in first table. The msn table would be updated but since the I ran out of room in the first table the 4th ticker would not appear. What I would like is something that adds a row between the first and second table and drops down the formulas so that everything remains uniform. I hope I explained it well enough. Any ideas.