Help with VBA Macro to automatically copy/paste as Values

[Anand]

I have a worksheet with an inherent circular calculation. To get the answers
to converge, I need to copy D85:W85 to row 55 as Values and hit shift+F9. I
keep doing this (about 5-6 times) till the numbers in row 55 and 85 are less
than 100 off.

Is there a way to automate this process with either a function or a macro?

Thanks in advance,

Anand

[MattShoreson]

From your post it's unclear what process you want automated.

What values/formulae are in row 55, 85?
What's the circular calcuation?

[Anand]

Matt:
The worksheet is a part of a large financial model that projects a
partnership's tax capital accounts for the next 20 years.

The amount of non-recouse debt is reduced by the amout of deficit
restoration obligation a partner accepts (Row 85), which in turn depends on
the amount of non-recourse debt. This leads to the circularity.

I need a macro/vba script that can copy the Defit obligation in that is in
row 85 (D85:W85) and paste-special as values only on to Row 55 (D55:W55) and
hit ****-F9 to recalculate the worksheet only. The amount of non-recourse
debt is reduced by the values in Row 55. Once the sheet is recalculated, the
values in Row 85 change. I keep doing this copy-paste-specialValues about 5-6
times and hit shift F9 each time and the difference between Row55 and Row 85
becomes small (say less than 100).

I hope this is clearer ...

-Anand
"MattShoreson" wrote:

>
> From your post it's unclear what process you want automated.
>
> What values/formulae are in row 55, 85?
> What's the circular calcuation?
>
>
> --
> MattShoreson
> ------------------------------------------------------------------------
> MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
> View this thread: http://www.excelforum.com/showthread...hreadid=470649
>
>

[Baapi]

Sub Paste_Values()

Application.Calculation = xlCalculationManual
Range("D85:W85").Copy
Range("D55").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False
Activesheet.Calculate

End Sub

Baapi,
Bangalore