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.

 

[Roccofan]

GOT IT!!!

The following code worked:

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

Thanks to Lenze and KniteMare for all the help.

RF
This message was edited by Roccofan on 2002-08-27 11:32

 

[KniteMare]

Then also DIRECTLY reference that cell by changing the associated line to:

Sheets("Sheet1").Activate
Sheets("Sheet1").Range("F4").Select

Yours in EXCELent Frustration

KniteMare

If it was easy then everybody would be doing it.