Yet another one right over the plate

Roccofan

Board Regular
Joined
Apr 18, 2002
Messages
61
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.
 

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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top