Worksheet object in Excel 97 v's Excel 2000

[SQLBI]

Hi,

I've developed an application in Excel 2000 which uses the .Copy method of the Worksheet Object.

In Excel 2000 this works fine, but when run in Excel 97 it throws up a runtime error "Method of worksheet object failed".

The only difference i can see between the two versions is the Object libraries; Excel 8.0 for 97 and Excel 9.0 for 2000, but surely the .Copy method is standard for most of the later versions of Excel.

If anyone has any ideas as to what the problem maybe, please let me know.

My code is as follows:

Dim team As String
Dim value As Double
Dim file As String

team = ActiveSheet.ComboBox1.value
value = Cells(35, 4)
file = "C:\Transfer - Team " & team & ".xls"

If team <> &quot;&quot; Then

If Cells(23, 4).value <> 0 And Cells(33, 4).value <> 0 Then

If value <= 50 Then

Sheets(&quot;Transfers&quot.Select
Sheets(&quot;Transfers&quot.Copy 'Code fails here in Excel 97
ChDir &quot;C:\&quot;
ActiveWorkbook.SaveAs Filename:=file, FileFormat:=xlNormal, _
Password:=&quot;&quot;, WriteResPassword:=&quot;&quot;, ReadOnlyRecommended:=False, _
CreateBackup:=False

ActiveSheet.Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

ActiveWorkbook.Close

MsgBox &quot;Your transfer has been saved as: &quot; & file & _
vbCrLf + &quot;Please email this file to Leigh Moore (leigh.moore@visionexpress.com) for it to be recorded&quot;, vbOKOnly + vbInformation, &quot;Transfer Created&quot;

Else

MsgBox &quot;Unable to create transfer, team value exceeds maximum allowed.&quot;, vbOKOnly + vbExclamation, &quot;Error&quot;
Exit Sub

End If

Else

GoTo TransError

End If

Else

GoTo TransError

End If

TransError:

MsgBox &quot;Transfer is incomplete, please rectify&quot;, vbOKOnly + vbExclamation, &quot;Error&quot;
Exit Sub

Thanks in advance.

Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[SkipVought]

Hi,

I have used Worksheets(SheetName).Copy in 97 with limited success.

However, I have run into the 255 byte per cell limitation.

A work around for that is

Set wsOld = ActiveSheet
Workbooks.Add
Set wsNew = ActiveSheet
wsOld.UsedRange.Copy _
  Destination:=wsNew.Cells(1,1)

Don't know if this will help, but I hope

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com