Excel 2000 to Excel 97 macro problem

[Alan T]

I've created a spreadsheet that will be used to log details of calls
coming into a helpdesk. The spreadsheet is going to be used to provide
management information about the nature and type of calls.

I'm trying to move all data from one sheet onto another sheet in the
same workbook (sheets CLT and CS). I'm looking to transfer data from
cells J8,J10,J12,J14,J16 on sheet CLT to sheet CS when a button is
clicked. When the button is pressed for the first time the data should
record on Sheet CS in Cells A2,C2,E2,G2,I2 the second time the button
is pressed the data should record in cells A3,C3,E3,G3,I3 the next time
A4,C4,E4,G4,I4 etc

I have some validation checks being run when the button is pressed at
the moment and these are required going forward. The button also resets
the fields providing validation is succesful.

I've written the following macro with help from users here which allows
me to do what I want:-

Sub UpdateNow()
'
' UpdateNow Macro
' Macro recorded 29/10/2003 by Alan Towle
'
Application.ScreenUpdating = False
If Range("j8") = 1 Then response = MsgBox("Please record your
Initials", 0, "Error!")
If response = vbOK Then GoTo cancelled

If Range("j10") = 1 Then response = MsgBox("Please record the Branch
Details", 0, "Error!")
If response = vbOK Then GoTo cancelled

If Range("j12") = 1 Then response = MsgBox("Please record the
Application Details", 0, "Error!")
If response = vbOK Then GoTo cancelled

If Range("j14") = 1 Then response = MsgBox("Please record the nature of
the Query", 0, "Error!")
If response = vbOK Then GoTo cancelled

If Range("j16") = "" Then response = MsgBox("Please record free-format
message", 0, "Error!")
If response = vbOK Then GoTo cancelled

response = MsgBox("Are you sure you want to log your call?", 1, "Log
your call?")
If response = vbCancel Then GoTo cancelled

Sheets("Call Logging Tool").Range("j8").Copy _
Sheets("Call Statistics").Range("A65536").End(xlUp).Offset(1, 0)
Sheets("Call Logging Tool").Range("j10").Copy _
Sheets("Call Statistics").Range("C65536").End(xlUp).Offset(1, 0)
Sheets("Call Logging Tool").Range("j12").Copy _
Sheets("Call Statistics").Range("E65536").End(xlUp).Offset(1, 0)
Sheets("Call Logging Tool").Range("j14").Copy _
Sheets("Call Statistics").Range("G65536").End(xlUp).Offset(1, 0)
Sheets("Call Logging Tool").Range("j16").Copy _
Sheets("Call Statistics").Range("i65536").End(xlUp).Offset(1, 0)
Range("j8,j10,j12,j14").Select
Selection.FormulaR1C1 = "1"
Range("j16").Select
Selection.FormulaR1C1 = ""

cancelled:
Application.ScreenUpdating = True
'
End Sub

The problem is that I have written this using on Excel 2000 at home and
some of the machines at work, on which the sreadsheet will be used have
Excel 97 installed. On 97 the macro is buggy and will run correctly the
first time, but on the second time running it will generate an error
code. If it is reset during the debug it runs fine for the first time
again, however fails on second run again.

I take there is something in the macro which isn't compliant with 97.
Can someone help me identify what it is?? Also can someone suggest a
workaround for this??

Thanks

 

[Henry]

Alan,

Try
Sub UpdateNow()
' UpdateNow Macro
' Macro recorded 29/10/2003 by Alan Towle
'
Dim response
Application.ScreenUpdating = False
If Range("j8") = 1 Then
response = MsgBox("Please record your Initials", 0, "Error!")
If response = vbOK Then
Application.ScreenUpdating = True
Exit Sub
End If
End If

If Range("j10") = 1 Then
response = MsgBox("Please record the Branch Details", 0, "Error!")
If response = vbOK Then
Application.ScreenUpdating = True
Exit Sub
End If
End If

If Range("j12") = 1 Then
response = MsgBox("Please record the Application Details", 0, "Error!")
If response = vbOK Then
Application.ScreenUpdating = True
Exit Sub
End If
End If

If Range("j14") = 1 Then
response = MsgBox("Please record the nature of the Query", 0, "Error!")
If response = vbOK Then
Application.ScreenUpdating = True
Exit Sub
End If
End If

If Range("j16") = "" Then
response = MsgBox("Please record free-format message", 0, "Error!")
If response = vbOK Then
Application.ScreenUpdating = True
Exit Sub
End If
End If

response = MsgBox("Are you sure you want to log your call?", 1, "Log your
call?")
If response = vbCancel Then
Application.ScreenUpdating = True
Exit Sub
End If


Etc, Etc......

HTH
Henry

 

[Alan T]

I don't think the problem lies in the first half of the macro as the
error code refers to the following line

Sheets("Call Logging Tool").Range("j8").Copy _
Sheets("Call Statistics").Range("A65536").End(xlUp).Offset(1, 0)

It displays the following error message

run-time error '1004'

copy method of range class failed

Any suggestions??

 

[Henry]

Alan,

Try

Sheets("Call Logging Tool").Range("j8").Copy _
Destination:= Sheets("Call
Statistics").Range("A65536").End(xlUp).Offset(1, 0)


HTH
Henry

 

[Dave Peterson]

I didn't look closely at your code, but 1004 errors in xl97 (when the code runs
successfully in xl2k) may mean that you've been hit by a bug that was fixed in
xl2k.

Do you run your code from a control from the controltoolbox (from a worksheet)?

If yes, then try changing the .takefocusonclick to false. If your control
doesn't have this (a commandbutton does), then add this to the top of your code:

activecell.activate