runtime error '1004' delete Method of Range Class Failed

[Jack]

Hi,

I copy and paste from a worksheet to another. It works
well on excel2000 but not on excel97-->runtime
error '1004' delete Method of Range Class Failed. Does it
means the versions are not compatible?.Do you know what is
wrong?!

this is what the button has:

Dim ExecComments As Worksheet
Set ExecComments = Sheets("Comments")

Dim Answers As Worksheet
Set Answers = Sheets("Sheet1")

'delete column
ExecComments.Columns(1).EntireColumn.Delete

request = InputBox("Please enter the question number to
see the comments (1-4)", "", "")

' Find the last row of data
FinalRow = Answers.Range("A65536").End(xlUp).Row


' Loop through each row
For X = 1 To FinalRow

thisValue = Answers.Range("F" & X).Value
If thisValue = "1" Then
thisValueComments = Answers.Range("Z" &
X).Value
If Not thisValueComments = "[NULL]" Then
Answers.Range("Z" & X).Copy

NextRow = NextRow + 2
If flag = 1 Then
ExecComments.Range("A12").Value = "QUESTION
1. what team you have? "
flag = 0
End If

ExecComments.Range("A" & NextRow).Value =
thisValueComments

End If
End If
Next X

Thanks for your help!!!
Jack

 

[kkknie]

I may be wrong, but I seem to remember this problem happening to me whe
I used an older version. Try this:

'delete column
ExecComments.Select
Columns(1).EntireColumn.Delete
Sheets("YourOriginalSheetNameHere").Select

I don't have '97 anymore, so cannot test (but the code should work o
all versions). Maybe someone else can shed some more light on it.

 

[Tom Ogilvy]

How are you triggering the code - if with a commandbutton, try changing the
takefocusonclick property to false - just a guess of course.

 

[Tom Kennedy]

How are you triggering the code - if with a commandbutton,

try changing the takefocusonclick property to false
- just a guess of course.

Yes, Tom. And a good guess at that! Relatively inexperienced a
applying VB to Excel (97), I've been fighting this problem for too man
hours with MS "Help" offering no clue as to WHY the delete failed wit
every variation I put in the command. Taking the focus off the butto
did the trick, and in retrospect it became clear as to why the delet
failed. Thanks very much!

To