This is fairly straightforward -

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 Then
Sheets("Sheet1").Select
Range("D1").Select
End If
End Sub

but for the range select I get Runtime Error 1004 -- Select Method of Range
Class Failed. I don't necessarily even want to go to Sheet1, but I get the
same problem with eg:
If Target.Column = 1 Then
Range("Sheet1!D1").Value = Range("B2").Value

I've seen replies to other questions on Worksheet_Change where switching to
other sheets is OK, so I'm assuming it's something to do with my
installation. Any suggestions gratefully received.

TIA,
Andy

try
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 1 Then Exit Sub
Application.Goto reference:=[Sheet1!d1]
End Sub

"Andy Brown" wrote in message
...
This is fairly straightforward -

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 Then
Sheets("Sheet1").Select
Range("D1").Select
End If
End Sub

but for the range select I get Runtime Error 1004 -- Select Method of
Range
Class Failed. I don't necessarily even want to go to Sheet1, but I get the
same problem with eg:
If Target.Column = 1 Then
Range("Sheet1!D1").Value = Range("B2").Value

I've seen replies to other questions on Worksheet_Change where switching
to
other sheets is OK, so I'm assuming it's something to do with my
installation. Any suggestions gratefully received.

TIA,
Andy

It is just 3 times slower - but does avoid the reference problem.

Regards,
Tom Ogilvy

Don Guillett wrote in message
...
I didn't suggest you use application with this. Only if using the goto
reference.
[Sheet1!D65536].End(xlUp).Offset(1, 0).Value = [B2]
should work as well.

"Andy Brown" wrote in message
...
Gents, I simplified the question in the interests of focussing on the
main
problem. I was actually after shunting Sheet2!B1 to the first free cell
in
Sheet1!D:D (with D1 as a label). Therefore,

(Don),
Application.[Sheet1!D65536].End(xlUp).Offset(1, 0).Value =
Range("B2").Value
worked fine ;

(Mr Ogilvy),
Sheets("Sheet1").Range("D65536").End(xlUp).Offset( 1, 0).Value =
Range("B2").Value
worked fine, and the error explanation was excellent.

Thanks much to you both,
Andy

Tom,
I have a fast computer.<G

"Tom Ogilvy" wrote in message
...
It is just 3 times slower - but does avoid the reference problem.

Regards,
Tom Ogilvy

Don Guillett wrote in message
...
I didn't suggest you use application with this. Only if using the goto
reference.
[Sheet1!D65536].End(xlUp).Offset(1, 0).Value = [B2]
should work as well.

"Andy Brown" wrote in message
...
Gents, I simplified the question in the interests of focussing on the
main
problem. I was actually after shunting Sheet2!B1 to the first free
cell
in
Sheet1!D:D (with D1 as a label). Therefore,

(Don),
Application.[Sheet1!D65536].End(xlUp).Offset(1, 0).Value =
Range("B2").Value
worked fine ;

(Mr Ogilvy),
Sheets("Sheet1").Range("D65536").End(xlUp).Offset( 1, 0).Value =
Range("B2").Value
worked fine, and the error explanation was excellent.

Thanks much to you both,
Andy