Help me covert script from Excel 2003 to Excel 2000

[rickyckc]

Hi all,

I have a script that works on my PC that's using Excel 2003. When my friend runs it on his PC with Excel 2000, he hits a run-time error '1004' error message that says 'Application-defined or object-defined error'. Is somebody able to help me convert and get it working on a Excel 2000 ? Below is the script

Sub MCY()
'
' Macro done on 14/08/2007 by Ricky
'
    Sheets("Sales Staff").Select
    Cells.Select
    Selection.ClearContents
    Range("A1").Select
    Sheets("Sales Order").Select
    Cells.Select
    Selection.ClearContents
    Range("A1").Select
    Workbooks.Open Filename:= _
    "C:\MCY\SalesOrder_Raw.xls"
    Windows("SalesOrder_Raw.xls").Activate
    Cells.Select
    Selection.Copy
    Windows("Sales.xls").Activate
    Sheets("Sales Order").Select
    ActiveSheet.Paste
    Range("A1").Select
    Windows("SalesOrder_Raw.xls").Activate
    Application.DisplayAlerts = False
    ActiveWindow.Close
    Application.DisplayAlerts = True
    Range("I2").Select
    ActiveCell.FormulaR1C1 = _
        "=RC[-5]&"" ""&RC[-6]&LEFT(RC[-3],FIND(""oz"",RC[-3])+1)"
    Range("I2").Copy Destination:=Range("I2:I" & [E65536].End(xlUp).Row)
    Application.CutCopyMode = False
    Columns("C:D").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sales Staff").Select
    Range("D1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Key2:=Range("D2") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
    Range("D1:E17").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "A1"), Unique:=True
    Selection.ClearContents
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]&"" ""&RC[-2]"
    Range("C2").Copy Destination:=Range("C2:C" & [B65536].End(xlUp).Row)
    Application.CutCopyMode = False
    Columns("C:C").Select
    Selection.Columns.AutoFit
    Rows("2:2").Select
    ActiveWindow.FreezePanes = True
    Range("C2:C7").Select
    Application.CutCopyMode = False
    On Error Resume Next
    Names("SalesStaff").Delete
    Names("Extract").Delete
    On Error GoTo 0
    ActiveWorkbook.Names.Add Name:="SalesStaff", RefersToR1C1:= _
"='Sales Staff'!R2C3:R" & Sheets("Sales Staff").Cells(Rows.Count, "C").End(xlUp).Row & "C3"
    Range("A1").Select
    Sheets("Sales Order").Select
    Range("A1").Select
    Sheets("Sales Template").Select
    Range("A1").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
    Selection.ClearContents
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=SalesStaff"
        .IgnoreBlank = False
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

Thanks.

Best Regards,
Ricky

 

[Andrew Poulsom]

Try removing:

, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal

from your Sort statement. These arguments are new in Excel 2003. The removal won't have any effect on your procedure.

 

[rickyckc]

Hi Andrew,

Thanks alot for your time. Will get my friend to try it out.

Best Regards,
Ricky

 

[rickyckc]

Hi Andrew,

BTW, i forgot how is it again to get the macro start running when the file is open.... is it changing first line to 'Sub autoexec()' ?

Thanks.

Best Regards,
Ricky

 

[Andrew Poulsom]

In a general module the procedure's name is Autpen. But in more recent versions of Excel you should use the Workbook_Open event procedure in the ThisWorkbook module.

 

[rickyckc]

Oh ya, thanks Andrew.

Btw, I have another Office 2000 problem, was wondering if you can help me.

In my Office 2003, I am able to create a cert and setup my excel to recognise my digital signatures. However, when I tried to help my friend on his Office 2000, i realised the Tools->Option GUI is different.

Can you help me to come out with a simple steps for this setup for Office 2000 ?

Thanks.

Best Regards,
Ricky

 

[Andrew Poulsom]

Here you are:

http://support.microsoft.com/kb/206637

 

[rickyckc]

Hi Andrew,

Thanks. I already know that part. What I'm looking for are the steps to configure excel in the Tools->Option part, to recognise my friend's signature. I believe it's different between 2003 and 2000.

Best Regards,
Ricky

 

[Andrew Poulsom]

Is it?

http://office.microsoft.com/en-us/ork2000/HA011381051033.aspx

 

[rickyckc]

Hi Andrew,

Thanks again ! I've been searching high and low on MS websites, but just can't seems to find your second link.



Best Regards,
Ricky