VBA to copy cell (with formula) to next row and then copy and paste values in cells above

Hi, i have a range of data in AG3:AG14, each month i copy the formula in the last row with data (i.e AG7) to the next row below i.e (AG8) and then copy and paste as values the cells above. How can i automate this with a macro?
let me know if any further info is required. i.e below - i want to copy the formula (which gives me 138%) into the next available row and then copy and paste the above cell as a value. i do this once a month.

Jan-13 159%
Feb-13 120%
Mar-13 113%
Apr-13 107%
May-13 138%
Jun-13
Jul-13
Aug-13
Sep-13
Oct-13
Nov-13
Dec-13

Hi, used your code but it didnt work. it didnt come up with an error message but nothing seems to have happened? Regards.

Hi Palmetto. I created a test spreadsheet and tried it and it worked! thx so much.
what i need to do now is amend the code so that if i have two ranges in the same colmun to perform the same function for both ranges - using one macro.

i have attached the file below. how would the code be written? ican then try and manipulate for other columnd and worksheets too.

WOW! You are a genius. Thanks for this Palmetto. I have started to add to this code so that if I have other ranges of data in the same worksheet I can copy the last row and it worked great.

Hopefully my final question is how do I repeat a similar step but in a different worksheet (in the same macro)? I would like to copy the FORMULA in the last row of columns K to N of the worksheet "Sales Ops Data" into the next row below and then make the row above as values...I tried to do it in the same macro (as a test on column L) but it doesn't work

Also once i have completed this last step in "Sales Ops Data" I want to make the row above white (i.e May) and the row below (i.e June) light Green - (to show this is the current month) if this last step is too hard then don't worry.

Thx!

Hi...is this achieveable in VBA? Any advice would be appreciated. I am half way there with the code...

I have managed to work out how to do this for another sheet in one macro. The last part is to do with the formatting of the cells, i.e:

highlight the current month row green and make last month's row white...any tips?