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.
--
HTH...

Jim Thomlinson


"Access n00b" wrote:

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

Thanks, that was the culprit!

"Jim Thomlinson" wrote:

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.
--
HTH...

Jim Thomlinson


"Access n00b" wrote:

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