Inserting values to tables using VBA - ERROR attempting to shift cells in a table

Question

Hello,

I built a sub that inserts values into 2 tables using Excel VBA:

Before, when I first wrote the sub, I had the 2 tables side by side and everything worked fine. However, I am experimenting with a tester sheet. The actual sheet I am about to build will have over 20 tables, one below the other. So I decided to test this code by puting the tables below one another and see what happens. 

From the picture above, you can see that I am getting an error that reads: "Run-time error '1004': This operation is not allowed. The operation is attempting to shift cells in a table on your worksheet."

Based on my understanding, the main problem is because the tables are above / below each other. Using Excel VBA, is there a way to insert new rows into tables (one below another) without getting this error? I am dealing with DYNAMIC data, so each table could have 20 rows today but 30 rows tomorrow if new devices come into the market. Thus, I can't use code with fixed sized tables.

Here is a small snippet of my code, trying to insert values into the top table called "MasDevTbl":

    Dim MasDevObjRow As ListRow ' Master Device List Row

  Set MasDevObjRow = MasDevTbl.ListRows.Add ' **** ERROR HERE

    ' For Range.(1,1) - first "1" means inserting to next row available

    ' and second "1" refers to the column index

    MasDevObjRow.Range(1, 1).Value = deviceName ' ex. "Pressure Sensors"

    MasDevObjRow.Range(1, 2).Value = deviceType ' ex. "MEMS"

LINK TO DROPBOX: https://www.dropbox.com/s/

Please let me know if anybody has ideas! Anything is much appreciated!!

Thanks.

Answer 1

"Run-time error '1004': This operation is not allowed. The operation is attempting to shift cells in a table on your worksheet."

The reason for this error is that the table below has 3 columns and the top table has 2 columns.

If you add a row to a table using ListRows.Add means only 2 cells are inserted into the sheet in this case, which would destroy the table below.

The solution is to insert an entire row and resize the table.

Andreas.

Option Explicit

Sub Main()

  AddTableRow ActiveSheet.ListObjects(1)

  AddTableRow ActiveSheet.ListObjects(2)

End Sub

Sub AddTableRow(ByVal Table As ListObject)

  Dim R As Range

  'Get all cells in this table

  Set R = Table.Range

  'Get the bottom right cell

  Set R = R.Resize(1, 1).Offset(R.Rows.Count - 1, R.Columns.Count - 1)

  'Insert a row below

  R.Offset(1).EntireRow.Insert

  'Resize the table

  Table.Resize Range(Table.Range, R.Offset(1))

End Sub

Answer 2

Hi Andreas,

Your solution definitely makes sense, to add an entire row & resize FIRST before adding in the rows. I started learning VBA this week, so I am new to the debugging. I am getting a run-time error that reads "Method 'Range' of object '_Worksheet' failed." 

Note the picture below is already populated with values, I was trying to insert a new device & got the error:

From this line of the code you specified:

I was wondering if you had any thoughts on how to fix this?

Answer 3

Application.ScreenUpdating = False

Cells(r, c + 1).Value = "helper"

For x = 1 To N

obj1.ListRows.Add

Is this supposed to be a joke, right?

What if the table below has more then 3 columns? Or if we have 2 tables below beside each other?

Andreas.