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
Thanks.
Best Regards,
Ricky
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