Hello,
I am attempting to write a macro in Excel 03 that copies a formula from one cell, D30, and paste this into cell d31 and then down the column to cell D109 and then copy all of this and paste it into the same cell location as values. Then when i update my reports the next day I want it to still copy the formula in D30 but then paste this into cell e 31 and down that column to c109 and so on.
I have got the macro to paste the formula into d31 and down to d109 but the next time i run it it only pastes into c31 but not down the column;

here's the code i am using;

Sub PasteValToNextColumn()

Application.ScreenUpdating = False

Dim Nextcolumn As Range
Set Nextcolumn = Sheets("01151 COS").Range("b31").End(xlToRight).Offset(0, 1)
Sheets("01151 COS").Range("d30").Select
Selection.Copy
Nextcolumn.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("01151 COS").Range("d31").Select
Selection.Copy
Range("D32:D109").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("01151 COS").Range("d31:d109").Select
Selection.Copy
Range("D31:D109").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False



End Sub

Any assistance would be greatly appreciated

Thanks

Configuration: Windows XP Internet Explorer 6.0