Adding a new table to Excel using macro/VBA

Question

I have a macro that inserts a new table in to a range, and names the new table based on another cells value:

Select Code  copy to clipboard
Sub sbCreatTable()
Sheet1.ListObjects.Add(xlSrcRange, Range("B1:B3"), , xlYes).Name = Range("a2").Text

However the sheet I would like to add the table to, has a range of tables already, all on Row B, so I need the table to be added to the end of these, and inserted on the first empty cell on row B. The code I have will only insert this in a specific range.

Could anybody assist with pointing me in the right direction?

Did you mean 'all on column B'? Do you want the new table on the second blank row at the bottom of all the tables in column B? — user4039065
Just read the reply, it was a late night! Sorry. I meant that it was to be to the RIGHT of column B on the same row (row 2). Adding a table would add it to C2, running it again would add a new table to D2, and so on. Hopefully this makes more sense! — James

Unknown Unknown · Accepted Answer · 2016-03-04T11:05:56

I've interpreted your narrative to state that you want the new table immediately to the right of the other tables starting in the second. I've found that it is best to keep one blank column as a 'moat' between structured ListObject tables.

With Sheet1
    With .ListObjects.Add(xlSrcRange, .Cells(2, Columns.Count).End(xlToLeft).Offset(0, 2).Resize(3, 1), , xlYes)
        .Name = .Parent.Range("a2").Text
    End With
End With

If you want to remove the blank column, change the Range.Offset property to .Offset(0, 1).

Adding a new table to Excel using macro/VBA

1 Answers