We are in the process of switching from running Excel 2007 under Windows XP to running Excel 2010 under Windows 7. Most of my Excel reports are not having any issues. However, I have several macros that cause Excel to crash when they go to save the file. Through trial & error, I have found that the problem seems to occur after deleting custom lists that were created earlier in the macro. Here's the pertinent code:
' Declare Variables
Dim VarListNum1
Dim VarListNum2
Dim VarListNum3
' Create Custom Lists
Sheets("Custom Lists").Select
Range("A2:A9").Select 'Order for Yesterday Info
Application.AddCustomList ListArray:=Range("A2:A9")
VarListNum1 = Application.CustomListCount
VarListNum1 = VarListNum1 + 1
Range("A14:A20").Select 'Order for Yesterday-1 Info
Application.AddCustomList ListArray:=Range("A14:A20")
VarListNum2 = Application.CustomListCount
VarListNum2 = VarListNum2 + 1
Range("A26:A33").Select 'Order for Month-to-Date Info
Application.AddCustomList ListArray:=Range("A26:A32")
VarListNum3 = Application.CustomListCount
VarListNum3 = VarListNum3 + 1
' Order Raw Data
On Error Resume Next
Sheets("Sheet1").Select
Range("A1:H200").Select
Selection.Sort Key1:=Range("A1:H200"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=VarListNum1, MatchCase:=False, Orientation:=xlLeftToRight
Range("J1").Select
Selection.Sort Key1:=Range("J1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=VarListNum1, MatchCase:=False, Orientation:=xlLeftToRight
Sheets("Sheet2").Select
Range("A1").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=VarListNum2, MatchCase:=False, Orientation:=xlLeftToRight
Range("J1").Select
Selection.Sort Key1:=Range("J1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=VarListNum2, MatchCase:=False, Orientation:=xlLeftToRight
Sheets("Sheet7").Select
Range("A1").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=VarListNum3, MatchCase:=False, Orientation:=xlLeftToRight
Range("J1").Select
Selection.Sort Key1:=Range("J1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=VarListNum3, MatchCase:=False, Orientation:=xlLeftToRight
'Delete Custom Lists
Application.DeleteCustomList (VarListNum3 - 1)
Application.DeleteCustomList (VarListNum2 - 1)
Application.DeleteCustomList (VarListNum1 - 1)
When I execute the macro line-by-line, it is creating & deleting the custom lists as expected. However, if I try to save the file after any of the custom lists have been created, Excel crashes. Has something changed in Excel 2010? Any suggestions for fixing or further troubleshooting?
Thank you,
' Declare Variables
Dim VarListNum1
Dim VarListNum2
Dim VarListNum3
' Create Custom Lists
Sheets("Custom Lists").Select
Range("A2:A9").Select 'Order for Yesterday Info
Application.AddCustomList ListArray:=Range("A2:A9")
VarListNum1 = Application.CustomListCount
VarListNum1 = VarListNum1 + 1
Range("A14:A20").Select 'Order for Yesterday-1 Info
Application.AddCustomList ListArray:=Range("A14:A20")
VarListNum2 = Application.CustomListCount
VarListNum2 = VarListNum2 + 1
Range("A26:A33").Select 'Order for Month-to-Date Info
Application.AddCustomList ListArray:=Range("A26:A32")
VarListNum3 = Application.CustomListCount
VarListNum3 = VarListNum3 + 1
' Order Raw Data
On Error Resume Next
Sheets("Sheet1").Select
Range("A1:H200").Select
Selection.Sort Key1:=Range("A1:H200"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=VarListNum1, MatchCase:=False, Orientation:=xlLeftToRight
Range("J1").Select
Selection.Sort Key1:=Range("J1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=VarListNum1, MatchCase:=False, Orientation:=xlLeftToRight
Sheets("Sheet2").Select
Range("A1").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=VarListNum2, MatchCase:=False, Orientation:=xlLeftToRight
Range("J1").Select
Selection.Sort Key1:=Range("J1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=VarListNum2, MatchCase:=False, Orientation:=xlLeftToRight
Sheets("Sheet7").Select
Range("A1").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=VarListNum3, MatchCase:=False, Orientation:=xlLeftToRight
Range("J1").Select
Selection.Sort Key1:=Range("J1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=VarListNum3, MatchCase:=False, Orientation:=xlLeftToRight
'Delete Custom Lists
Application.DeleteCustomList (VarListNum3 - 1)
Application.DeleteCustomList (VarListNum2 - 1)
Application.DeleteCustomList (VarListNum1 - 1)
When I execute the macro line-by-line, it is creating & deleting the custom lists as expected. However, if I try to save the file after any of the custom lists have been created, Excel crashes. Has something changed in Excel 2010? Any suggestions for fixing or further troubleshooting?
Thank you,