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

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

Seems to be a specific error, may be by your file layout?

I've tested the code within a new file and it works.

Please upload your file on an online file hoster like www.dropbox.com and post the download link here.

Andreas.

Answer 3

Hi Andreas,

I don't think it's the file layout. Here is the link: 

https://www.dropbox.com/s/h38y40n3quo776p/Insert%20Function%20Macro%20Enabled.xlsm?dl=0

Any help is much appreciated! Thank you.

Answer 4

I don't think it's the file layout. 

That's right, it's the code location.

If you call the Range-Object from within the code module of a sheet means the result Range should be within the sheet.

To simplify this:

   Set R = Range("A1")

executed from a regular module means R refers to the cell within the active sheet.

But if we place the code into the code module of MySheet means R refers always to MySheet!A1, regardless which sheet is active.

The solution is to specify the sheet object for the result Range object, change the line

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

to

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

But you have to make changes in the rest of your code, not in case of this issue, but to accomplish the other things.

Furthermore there is no need to Select anything. Please never use SELECT, SELECTION, ACTIVECELL, it is slow and error prone. Always refer to the objects directly.

See the code below as sample.

Andreas.

Private Sub CommandButton1_Click()

  Dim dataDestSheet As Worksheet

  Dim MasDevTbl As ListObject ' Master Device List Object

  Dim deviceName As String

  Dim deviceType As String

  Dim MasDevObjRow As ListRow

  ' Initialize variables

  Set dataDestSheet = Sheets("Data Destination")

  Set MasDevTbl = dataDestSheet.ListObjects("MasterDevice") ' Assign list object to MasterDevice table

  deviceName = Range("C4") ' deviceName is assigned to a value in cell C4

  deviceType = Range("C5") ' deviceType is assigned to a value in cell C5

  'Add a row

  AddTableRow MasDevTbl

  'refer to that row

  Set MasDevObjRow = MasDevTbl.ListRows(MasDevTbl.ListRows.Count)

  'Store the values

  MasDevObjRow.Range(1, 1).Value = deviceName

  MasDevObjRow.Range(1, 2).Value = deviceType

End Sub

Answer 5

Brilliant! You are a genius Andreas! Thank you very much!!