Runtime Error 1004: Application defined or object defined error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I recorded some basic code and did enough research to edit the code and make
it a little cleaner than when it was recorded. The macro is supposed to do
several things: 1) Unprotect all of the sheets; 2) Sort all of the sheets by
Dealer Name in ascending order; 3) Go to the end of the Dealership name list;
4) Insert the new Dealership name; 5) Sort all of the sheets again; 6) Make
the sheet with the macro buttons on it active. I did this in Excel 2003 and
now a user trying to run this macro in Excel 2000 is getting the following
error message:

Runtime Error 1004: Application defined or object defined error

Can anyone tell me how to make this work in Excel 2000?

Thanks!

Main Code:

Sub Insert_Dealership_Name()
'
' Insert_Dealership_Name Macro
' Macro recorded 03/25/2006 by William F. Knight
'

'
' Unprotect All Sheets
Unprotect_All_Sheets
' Request NewDealerName from user
Dim NewDealerName 'This line of code is optional
NewDealerName = InputBox("Enter the dealership's name.")
' Sort All Sheets By Dealership Names In Ascending Order
Dim SortCount 'This line of code is optional
Dim a 'This line of code is optional
SortCount = Application.Sheets.Count - 1
a = 1
Sheets(2).Select 'This line of code selects the 2nd sheet
Do Until a = SortCount
Range("A5:H5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("A6"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("B6").Select
ActiveSheet.Next.Select
a = a + 1
Loop
Range("A5:H5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("A6"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("B6").Select
' Insert NewDealerName on all sheets
Dim InsertCount 'This line of code is optional
Dim b 'This line of code is optional
InsertCount = Application.Sheets.Count - 1
b = 1
Sheets(2).Select 'This line of code selects the 2nd sheet
Do Until b = InsertCount
Range("A4").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = NewDealerName
Range("B6").Select
ActiveSheet.Next.Select
b = b + 1
Loop
Range("A4").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = NewDealerName
Range("B6").Select
' Sort All Sheets By Dealership Names In Ascending Order
Dim SortCount2 'This line of code is optional
Dim c 'This line of code is optional
SortCount2 = Application.Sheets.Count - 1
c = 1
Sheets(2).Select 'This line of code selects the 2nd sheet
Do Until c = SortCount2
Range("A5:H5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("A6"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("B6").Select
ActiveSheet.Next.Select
c = c + 1
Loop
Range("A5:H5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("A6"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("B6").Select
' Protect All Sheets
Protect_All_Sheets
' Make Instruction sheet the active sheet
Sheets("Inst").Select
End Sub

Unprotect_All_Sheets Sub

Sub Unprotect_All_Sheets()
'
' Unprotect_All_Sheets Macro
' Macro written 03/26/2006 by William F. Knight
'

'
Dim UnprotectCount 'This line of code is optional
Dim a 'This line of code is optional
Dim UnprotectPassword 'This line of code is optional
UnprotectCount = Application.Sheets.Count - 1
a = 1
UnprotectPassword = "53ConsumerLending"
Sheets(2).Select 'This line of code selects the 2nd sheet
Do Until a = UnprotectCount
ActiveSheet.Unprotect UnprotectPassword
ActiveSheet.Next.Select
a = a + 1
Loop
ActiveSheet.Unprotect UnprotectPassword
Sheets("Inst").Select
End Sub

Protect_All_Sheets Sub

Sub Protect_All_Sheets()
'
' Protect_All_Sheets Macro
' Macro written 03/26/2006 by William F. Knight
'

'
Dim ProtectCount 'This line of code is optional
Dim b 'This line of code is optional
Dim ProtectPassword 'This line of code is optional
ProtectCount = Application.Sheets.Count - 1
b = 1
ProtectPassword = "53ConsumerLending"
Sheets(2).Select 'This line of code selects the 2nd sheet
Do Until b = ProtectCount
ActiveSheet.Protect ProtectPassword
ActiveSheet.Next.Select
b = b + 1
Loop
ActiveSheet.Unprotect UnprotectPassword
Sheets("Inst").Select
End Sub
 
As a guess since you did not indicate where the error was occuring I would
say your problem stems from the sort. The last parameter you have listed is

DataOption1:=xlSortNormal

Which did not exist in xl2000. Just delete this parameter as it will have no
effect in 2003, since it is the default value.
 
Thanks, that was the culprit!

Jim Thomlinson said:
As a guess since you did not indicate where the error was occuring I would
say your problem stems from the sort. The last parameter you have listed is

DataOption1:=xlSortNormal

Which did not exist in xl2000. Just delete this parameter as it will have no
effect in 2003, since it is the default value.
Click to expand...
 
Back
Top