Worksheet change

[Andy Brown]

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

 

[Don Guillett]

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

 

[Tom Ogilvy]

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

Regards,
Tom Ogilvy

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.

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

 

[Don Guillett]

Tom,
I have a fast computer.<G>

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

Regards,
Tom Ogilvy

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.

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