I'm working on a financial model and I'd like to know if it was
possible to set up a button that would automatically do the following.
I am working with a numbers that run over a few weeks. Each week, we
get a new set of data. So basically, every week, I have to go to the
very last week and insert a blank row before it. Copy the values from
the week last week and paste them into the new column. Then, I would
update the links in the model so that the last week of data
automatically updates (This part does not necessarily have to be
included in the macro). So lets say that this is what I start with:
A B C D E*
1 1/1/06 1/8/06 1/15/06 1/22/06
2 Revenue 200 150 200 175
3 Expenses 50 100 75 50
4 Gross Profit 150 50 125 125

*This last row is based on a link and will automatically update by
updating the excel file that it is linked to.

Then I get data for the week ending 1/29/06. So basically I have been
opening up the old sheet and inserting a row in front of row E. Then I
copy the values (which were previously in column E) in column F and
paste them as values back in column E. Plus I'd like the keep the
formulas consistent so the dates calculate for every week so in this
case I would only copy the revenue, expenses, and gross profit line and
I would copy the formulas over from D1 which adds 7 days to the cell to
the left of it. So basically, the result would look like this. After
I would edit the links, column F would automatically take the new data
in.

A B C D E
F*
1 1/1/06 1/8/06 1/15/06 1/22/06 1/29/06
2 Revenue 200 150 200 175 250
3 Expenses 50 100 75 50 150
4 Gross Profit 150 50 125 125 100

Does this make sense? Please let me know if you need any
clarification. Thanks in advance!