Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro that will select and insert a new row and paste as value
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro that will select and insert a new row and paste as value
We receive new data from another party every week so I just go to
EditLinks 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. vezerid wrote: 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 wrote: 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro that will select and insert a new row and paste as value
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 wrote in message ups.com... We receive new data from another party every week so I just go to EditLinks 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. vezerid wrote: 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 wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|