Previously working code is now is now giving Application-defined or object-defined error

Hi all! I'm by no means an excel programming expert, so I've been unsuccessful in figuring out why my code that has always worked flawlessly is now unhappy...
I have a sheet called "Preparation" that has a table (columns A-L) and a few command buttons that automatically sort the table based on the desired column. One button that has stopped working sorts my table based on the date column (which is column K in this case). The button is called DateSort.
To construct the code for this button, I basically just recorded a macro and pasted that code into the sheet it pertains to. The code is extremely basic and has never given me problems. Now when I run it I get the 'Application-defined or object-defined error'. The .Apply line is highlighted.

One thought I had was I recently updated my machine from MS Office 2007 to 2013. I'm almost positive it was working at some point after the upgrade, but not 100%. Is there a reason the upgrade would cause the error? A setting that needs to be changed in options? Any help would be greatly appreciated!

I have uploaded a sample workbook, or here is the code located in Sheet1 (Preparation):

Please Login or Register  to view this content.

Hi and welcome to the forum.

Two suggestions: 1) add code tags to your post before a moderator picks it up and 2) upload a sample workbook so the code, and the fault, can be tested.

Regards, TMS

You need to add [Code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/Code] after your code.

Because ...

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

Works for me (in 2007) but the code could be a little more efficient:

Please Login or Register  to view this content.

I've commented out some parameters that are default options.

And you should change the properties of the button so that it doesn't move and size with cells ... or it can change shape and font size


See the updated example

Is there a reason the upgrade would cause the error?

Quite possibly but who knows what. Let's put it this way: I developed some code for a client in Excel 2007 and it ran perfectly, and quite quickly. When he ran it in Excel 2010 it took 40 times longer. That was down to Conditional Formatting on one of the sheets. I amended the code to clear the CF before the routine and re-apply it afterwards and it resolved the problem.

Sometimes, errors are highlighted that bear no relation to the actual problem. Check the references and make sure you don't have any flagged as MISSING.


Regards, TMS