Error when running macro to copy and insert row

[NerdNinja]

Really stumped here. I just recently switched to Excel 2013 and some of my favorite macros are now generating errors. The odd part is that the macro will work perfectly when I first open my workbook, but if I make ANY changes to the table at all, then attempt to run the macro again, I receive Run-time error '-2147417848 (80010108)' Automation error The object involved has disconnected from its clients. The macro below was written to copy the topmost row of a table, insert the row immediately above (so it becomes the top row of the table), clear the contents of each cell (skipping over cells containing formulas), and return the active cell to the beginning of the row.

Sub Add_Row()
'
' Add_Row Macro
'

'
Application.ScreenUpdating = False
Range("A1").Select
ActiveCell.Offset(2, 0).Rows("1:1").EntireRow.Select
Selection.Copy
Selection.Insert Shift:=xlDown
ActiveCell.Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, 2).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, 2).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, 2).Range("A1").Select
Selection.ClearContents
ActiveCell.Offset(0, -18).Range("A1").Select
Application.ScreenUpdating = True
End Sub

The macro always seems to break on Selection.Insert Shift:=xlDown. Again, I can run the macro successfully when I first open the workbook, but if I add the copied/cleared row at the top and add information to it, then try to run the macro to add another copied/cleared row for my next entry, I get the error. Additional pain is that it crashes my excel and I have to close all workbooks (saved or not) and reopen before I can resume working. I've tried searching all over the web for a solution to this. HELP!

 

[XLRATOR]

Howdy,
First of all, this looks to be made by the macro recorder which makes it so odd.
so if you can explain what you want to do by examples, we may end up with some great code to make it look and work better.

 

[NerdNinja]

This was one of my earliest macros that I created, so it's probably more elementary than most. But I had been using it successfully in Excel 2010 for a couple years, so I never made any updates to it. However, when I recently transitioned to my new laptop (which uses Excel 2013) I transferred my spreadsheets to the new machine and noticed that some of the macros would not execute properly. The code above was recorded for a spreadsheet I use to track orders. The table contains formulas to calculate how long it takes for orders to be completed and shipped to the buyer. It also contains conditional formatting to color code the rows based on the status of the order. Unfortunately, it appears I cannot attach the workbook to this thread, so I've listed a very BASIC description of the table. Note, the code above is the exact code I use on the real tracking sheet, below are just examples of the fields and formulas I use.

Column headers:
A "Order #"
B "Name"
C "Date Ordered"
D "Days old" formula =IF($C2="","",IF($H2="SHIPPED","",TODAY()-$C2))
E "Item Description"
F "Expected Ship Date" formula =IF($C2="","",$C2+14)
G "Days left to ship" formula =IF($C2="","",IF($H2="SHIPPED","",$F2-TODAY()))
H "Status"
I "Date Shipped"
J "Order Completed in days" formula =IF($H2="SHIPPED",I2-C2,"")

The idea behind the macro was to make it easier to add a new row to the top of the table (so newest orders are always on top) with all the same formulas and conditional formatting. Before the macro, I would just copy the top row, and insert at the top of the table. Then go back and clear the cells without formulas so I could enter the new order information. I very simply just recorded those steps using the macro recorder and added a button for the macro to my quick access toolbar so it was a simple one-click step to create a blank entry row for new orders. But the macro keeps failing on the Selection.Insert Shift:=xlDown command. I'm totally open to a new way to achieve the same results! Any advice would be VERY much appreciated!!

 

[XLRATOR]

Here is a template simulating what you are trying to do, the code behind the button copies the data you enter in the input area, then sorts the orders by date from Newest to oldest.

https://dl.dropboxusercontent.com/u/100370669/Sales Demo With Auto Sort.xlsm

Good luck.