# Yet another one right over the plate

#### Roccofan

##### Board Regular
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

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
I might be missing something, but why not enter in \$A\$45 on Master Budget Sheet the formula ='Payoff Calculation-BOA'!\$F\$4

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

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?

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

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

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

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

HTH

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)
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.

RF

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)
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

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

Replies
1
Views
228
Replies
4
Views
267
Replies
2
Views
303
Replies
1
Views
281
Replies
0
Views
394

1,218,575
Messages
6,143,311
Members
450,477
Latest member
teresab543

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back