How to create a table using vba?

Question

I'm writing a simple code to get some key figures from a database import/export. I want to make it user friendly, basically "one buttton click" gives the result.

When I wrote the code, I use Alt+F11 and "Play" => it works. But when I inserted the Active X-Control "CommandButton1_Click" the macro stops.

Here is where (first line):

         ActiveSheet.ListObjects.Add(xlSrcRange, Range("A$1:$Be$1500"), , xlYes).Name = _
    "Table1"

Range("Table1[#All]").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight1"

This is exactly the same code which works with the play button.

I get the error message:

"Run-time Error 1004:

The worksheet range for the table data must be on the same sheet as the table beeing created."

Does anyone have an idea?

An improvement to my code would be, to have the table length variable: There might be more rows in one set, and fewer in another data set.

R3uK · Accepted Answer · 2015-10-08 08:45:09Z

1

An ActiveX command button is a Control in a Sheet, so you won't have any reference issues using it with your code but you must specify them to use the same code else where.

Tweak this to your need and let me know if it isn't enough :

Dim Ws As Worksheet
Set Ws = ThisWorkbook.Sheets("Sheet_Name")

Ws.ListObjects.Add(xlSrcRange, Ws.Range("A$1:$BE$1500"), , xlYes).Name = "New_Table_Name"
Ws.ListObjects("New_Table_Name").TableStyle = "TableStyleLight1"

Just adjust Sheet_Name and New_Table_Name to your need and that should roll!

answered Oct 8, 2015 at 8:45

R3uK

14.6k8 gold badges49 silver badges81 bronze badges

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

1 Comment

René Sass Over a year ago

Thanks, I thought it had something to do with this, but was not sure about the correct syntax.

Rory · Accepted Answer · 2015-10-08 09:02:37Z

1

You must specify the sheet for the source range too, because in a worksheet code module, any unqualified reference to Range or Cells is equivalent to Me.Range or Me.Cells and refers to a range on the sheet containing the code (ie the sheet with the button on in this case):

With ActiveSheet.ListObjects.Add(xlSrcRange, ActiveSheet.Range("A$1:$Be$1500"), , xlYes)
   .Name = "Table1"
   .TableStyle = "TableStyleLight1"
End With

answered Oct 8, 2015 at 9:02

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Collectives™ on Stack Overflow

How to create a table using vba?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related