I am getting the error below whenever I try to re-open a file that was created using vba script. I F8 through the code step by step and the error happens after a 3rd Data validation sequence. The first two have identical coding, run fine, and if I save the file after each point and then reopen the file, it opens fine (and shows the dropdowns that were created). If I continue stepping through, the file no longer reopens after the 3rd identical sequence (script is exactly the same). The only difference in the code is the number of items in the validation list. The first two lists have 2 and 9 items in the "list". The 3rd has 18 items in the list. I thought about using reference cells to create the list instead of writing the text into the code, but there would still be 18 objects referenced. Does that make a difference? If I save the file after letting it continue the macro and stopping after that 3rd sequence (they are all back-to-back), the file seems fine because it finishes "open". The dropdowns work perfectly. But if I save and close the file, then try to re-open it, I get the error below. Here is the code and the error. Any help is appreciated.
Error-CC.JPG
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="1211 InventoryNPC,1400 Construction in Progress,6271 Employee Benefits,7020 Advertising,7044 Supplies kitchen,7053 Office supplies,7054 Printing cost,7055 Postage/Mailing/Fed Ex,7056 Parking,7057 R&D expense,7061 Outside services,7063 Recruiting cost,7066 Auto expense,7068 Consultants,7070 Repairs and maintenance,7076 Sales Commission Expense,7116 Cell Phone,7127 Meals and entertainment,Miscellaneous"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Selection.ClearContents
Selection.Copy
Range("C5:C100", Range("C5:C100").End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Bookmarks