Yet another one right over the plate

[Roccofan]

My ultimate goal with this code is to prevent a circular reference by having the data in a cell on sheet 1 copied (using "Paste Values") to sheet 2. I need this done if/when the data on sheet one changes. I recorded a macro that I have to execute manually. I need to make it so that this is an automatic event when the data on sheet 1 changes:

Sub RiggsInterest()
Sheets("Payoff Calculation-BOA").Activate
Range("F4").Select
Selection.Copy
Sheets("Master Budget").Activate
Range("A45").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

Thanks in advance.

 

[lenze]

I might be missing something, but why not enter in $A$45 on Master Budget Sheet the formula ='Payoff Calculation-BOA'!$F$4

 

[Roccofan]

Hi Lenze,

That's what I tried to get across in the beginning of my post. I don't want to reference the cell directly because it will create a circular reference.

Any help would be appreciated.

RF

 

[lenze]

I'm confused. Is the value in $F$4 dependent on the value in $A$45? How does the value in $F$4 change? What are the formulas?

 

[Roccofan]

I think what I'm missing is a triggering event. Let me give a little more detail:

Sheet 1 is payoff calculation the has principal, interest, new purchases, interest.

Sheet 2 has the monthly payment amounts that feed sheet one.

On another sheet I have an assumption that allows me to increase the monthly payments. This in turn reduces the interest and causes the number of monthly payments to decrease. I want to have the monthly payments on sheet 2 reduced without creating a circular reference.

Maybe the macro can be triggered by telling it to run if the monthly payment amount on the assumptions page is changed. I think that will do it, but I don't have the first clue as the code that would be necessary.

Thanks again in advance for any assistance.

RF

 

[KniteMare]

It appears you want the Worksheet Change Event.
This will work although it is inefficient.
But it will get you started.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Sheets(1).Range("F4").Select
Selection.Copy
Sheets(2).Activate
Sheets(2).Range("A45").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub


Yours in EXCELent Frustration

KniteMare

 

[lenze]

You can use KniteMare's code. If your monthly payment cell on your assumption sheet is $A$4, then add this line to the macro

If Target.Address<>"$A$4" Then Exit Sub

The macro also must be in the sheet module for the sheet where the change is made.

HTH

 

[Roccofan]

KniteMare,

Thanks for your response. After my post I went to my Excel 2000 Progamming for dummies book and began to work with the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$13" Then
Sheets("Sheet1").Activate
Range("F4").Select
Selection.Copy
Sheets("Sheet2").Activate
Range("A45").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
End Sub

When I tried it I got an error message that read: Select method of range class failed.

Thanks again for any help you can provide.

RF

 

[KniteMare]

Excel is Frustrating (See Tag Line :-> )

While you are activating the Sheet the Program still wants to be told the EXACT address for Paste Special to take place. So add the line

Sheets("Sheet2").Range("A45").Select


As indicated


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$13" Then
Sheets("Sheet1").Activate
Range("F4").Select
Selection.Copy
Sheets("Sheet2").Activate
Sheets("Sheet2").Range("A45").Select
'Range("A45").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
End Sub

Yours in EXCELent Frustration

KniteMare
This message was edited by KniteMare on 2002-08-27 10:38

 

[Roccofan]

Thanks again KniteMare,

I'm still getting the same error messae. When I click "DeBug" the line:

Range("F4").Select


is highlighted in yellow.

RF