Run-time Error '1004' for Excel VBA tables

Question

I'm running Excel 365 and getting the following error when trying to run a macro that inserts rows into tables.

Run-time error '1004': This won't work because it would move cells in a table on your worksheet.

I am trying to create a macro that would add rows to multiple tables. I'm using this to add a row before a total row:

ActiveSheet.ListObjects("Table1").ListRows.Add AlwaysInsert:=True

I think the problem is that it returns that error because the addition of a row in table1 affects table2 and it can't insert a row there. Ultimately I want table 2 (and other tables) to have a row inserted so I've tried:

    ActiveSheet.ListObjects("Table1").ListRows.Add AlwaysInsert:=True


ActiveSheet.ListObjects("Table2").ListRows.Add AlwaysInsert:=True

But still returns the same error. I've tried reversing the order (adding to Table2 first) which executes the first command but then adding the row to table1 still returns the same error.

How do I add rows to multiple tables simultaneously?

The tables are stacked vertically, not side-by-side.

speadsheet with tables screenshot

Answer 1

That happens when the higher table has a footprint that is either narrower than the lower table, or the same size but not exactly aligned. What happens under the covers when you insert rows into a Table is that Excel not only moves the table rows at the insert point down but also tries to insert cells under the Table in order to move anything directly underneath the Table down by the same amount of rows so that space between the bottom of the Table and any content sitting below it is preserved.

If you have another Table under that top Table that is either larger than the top Table or the same size but offset by some amount of columns, Excel tries to insert rows below the 'footprint' of the Top Table, but finds it can't do this, because this would move only some of the columns of the bottom table down, but not the others.

You can see this behaviour in the following images.

Here, I've got a Table and some non-table content below it:

Watch what happens when I insert a new row into the Table:

Excel did it without complaining, but as you can see, only the cells immediately below the top table got moved downwards.

Now, if I had a second Table under the first instead, I can't do this, because you simply can't shift some of the columns of a Table down a row while leaving the remainder in the old row. Indeed, here's the error message I get when I try:

Some ways to program around this include:

• Switch the position of the Tables
• Amend your code to temporarily move the lower table somewhere else, do the insert on the higher table, and then move it back again.
• Insert an entire row in the sheet (which also inserts new rows in the Tables that get bisected)