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
– user4039065, Commented Mar 4, 2016 at 10:58
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
– James, Commented Mar 4, 2016 at 11:20

score 1 · Accepted Answer · 2016-03-04 12:29:28Z

1

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).

edited Mar 4, 2016 at 12:29

answered Mar 4, 2016 at 11:05

user4039065

Sign up to request clarification or add additional context in comments.

2 Comments

James Over a year ago

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 another table to D2, and so on. Hopefully this makes more sense!

user4039065 Over a year ago

I've adjusted the 'firs blank row' to a 'first blank column' above.

Collectives™ on Stack Overflow

Adding a new table to Excel using macro/VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related