Macro that will select and insert a new row and paste as value

[roderick.alfonso]

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!

 

[vezerid]

Lemme see if I understand:

The last column (row in your terminology) should have a formula like:
='[This Other Book.xls]Datasheet'!A2

This Other Book.xls is somehow updated externally. While a week IS the
last week, this formula is retained and updating from This Other Book
takes place whenever, e.g. you hit an F9. Once a week is not longer the
last week, and a new column is added, you want the data to freeze and
the NEW LAST column to have the same formula.

Please explain if my understanding reflects your situation.

HTH
Kostis Vezerides

 

[roderick.alfonso]

We receive new data from another party every week so I just go to
Edit>Links and then change the file from which I am pulling the data to
reflect the new numbers that the other part has sent us. Therefore,
the last column will update to reflect new data for the new week. I
want to be able to create a macro that will basically take the file
that I had last week and insert a new column with those links so that
the last week will reflect data from the new file and the week prior no
longer links at all.

Lemme see if I understand:

The last column (row in your terminology) should have a formula like:
='[This Other Book.xls]Datasheet'!A2

This Other Book.xls is somehow updated externally. While a week IS the
last week, this formula is retained and updating from This Other Book
takes place whenever, e.g. you hit an F9. Once a week is not longer the
last week, and a new column is added, you want the data to freeze and
the NEW LAST column to have the same formula.

Please explain if my understanding reflects your situation.

HTH
Kostis Vezerides

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!

 

[Jef Gorbach]

This should do it?

Sub Macro1()
Cells(2, 1).End(xlToRight).Activate 'goto last
revenue cell
ActiveCell.EntireColumn.Copy 'copy
source
ActiveCell.EntireColumn.Insert 'insert
blank column
ActiveCell.EntireColumn.PasteSpecial Paste:=xlValues 'then lock existing
values
Application.CutCopyMode = False 'unselect
source column
Cells(1, 1).Select
'put cursor at home
End Sub

We receive new data from another party every week so I just go to
Edit>Links and then change the file from which I am pulling the data to
reflect the new numbers that the other part has sent us. Therefore,
the last column will update to reflect new data for the new week. I
want to be able to create a macro that will basically take the file
that I had last week and insert a new column with those links so that
the last week will reflect data from the new file and the week prior no
longer links at all.

Lemme see if I understand:

The last column (row in your terminology) should have a formula like:
='[This Other Book.xls]Datasheet'!A2

This Other Book.xls is somehow updated externally. While a week IS the
last week, this formula is retained and updating from This Other Book
takes place whenever, e.g. you hit an F9. Once a week is not longer the
last week, and a new column is added, you want the data to freeze and
the NEW LAST column to have the same formula.

Please explain if my understanding reflects your situation.

HTH
Kostis Vezerides

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!