PDA

View Full Version : link cell in sheet1 to cell in sheet



alexanderd
07-13-2006, 09:36 AM
cannot get this to work can any one help


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Cells("A1") >= 1 Then
'Sheets("Sheet2").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = "=SUM(6*Sheet1!R[-1]C[-1])"
Range("B3").Select
End If
End Sub
the range B2 is then coppied down to end of row of A:A:bug:

OBP
07-13-2006, 09:42 AM
Why is
Sheets("Sheet2").Select
remmed out?

austenr
07-13-2006, 09:46 AM
Change If Cells("A1") to Range("A1")

mdmackillop
07-13-2006, 10:17 AM
I'm not clear why you want to run this every time you change your selection, or what "copied to the end of Row A:A" means
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If [A1] >= 1 Then
Sheets("Sheet2").Range("B2").FormulaR1C1 = "=SUM(6*Sheet1!R[-1]C[-1])"
With Sheets("Sheet2")
.Activate
.[B3].Select
End With
End If
End Sub

alexanderd
07-13-2006, 12:56 PM
I'm not clear why you want to run this every time you change your selection, or what "copied to the end of Row A:A" means
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If [A1] >= 1 Then
Sheets("Sheet2").Range("B2").FormulaR1C1 = "=SUM(6*Sheet1!R[-1]C[-1])"
With Sheets("Sheet2")
.Activate
.[B3].Select
End With
End If
End Sub

first of all let me say thank you for your reply.
the reason for running this every time is that the value of "A1" changes with the value of the euro. in order to get the operator to check the current value before he inputs an invoice, as the value of the cell is cleared on exit.

mdmackillop
07-13-2006, 03:14 PM
In that case I would use a different event code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Sheets("Sheet2").Range("B2").FormulaR1C1 = "=SUM(6*Sheet1!R[-1]C[-1])"
With Sheets("Sheet2")
.Activate
.[B3].Select
End With
End If
End Sub