Help me covert script from Excel 2003 to Excel 2000

rickyckc

Active Member
Joined
Apr 1, 2004
Messages
327
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

Code:
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
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Active Member
Joined
Apr 1, 2004
Messages
327
Hi Andrew,

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

Best Regards,
Ricky
 

rickyckc

Active Member
Joined
Apr 1, 2004
Messages
327
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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

rickyckc

Active Member
Joined
Apr 1, 2004
Messages
327
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
 

rickyckc

Active Member
Joined
Apr 1, 2004
Messages
327
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
 

rickyckc

Active Member
Joined
Apr 1, 2004
Messages
327
Hi Andrew,

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

(y)

Best Regards,
Ricky
 

Watch MrExcel Video

Forum statistics

Threads
1,122,914
Messages
5,598,840
Members
414,261
Latest member
KatieBsc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top