Only two columns showing when creating a table with selected data

Question

I am trying to create a table on each of seven worksheets. Each sheet is a territory; the first column of the table is a company in the territory and each subsequent column is a product sold to that company.

Although all the data I want is selected just before creating the table, the "create table" code only includes the first two columns.

I tried adding the following to fix this problem, but I get an error:

ActiveSheet.ListObjects.Add(xlSrcRange, Range([#All]), , xlYes).Name = _
    "Table1"

Why does this only include two columns?

Sub Find_Em()
  '
  ' Find_Em Macro
  '
  Dim Salesman(7) As String

  Salesman(0) = "Sally"
  Salesman(1) = "Sandra"
  Salesman(2) = "Susan"
  Salesman(3) = "Suki"
  Salesman(4) = "Samantha"
  Salesman(5) = "Sutra"
  Salesman(6) = "Sherly"

  i = 4

  Tbl_Name = Salesman(i) & "_QB_Table"

  'a clumsy way to find the data I actually want to put into the new table

  Cells(3, 150).Select
  Selection.End(xlToLeft).Select
  Last_Column = ActiveCell.Column
  Range(Cells(3, 12), Cells(3, Last_Column)).Select
  Range(Selection, Selection.End(xlDown)).Select

  ' the following actually makes the table. Although many columns are "selected" on
  ' the left-most 2 columns become part of the new table.

  ActiveSheet.ListObjects.Add(SourceType:=xlSrcRange, _
    Source:=Selection.CurrentRegion, _
    xlListObjectHasHeaders:=xlYes _
    ).Name = Tbl_Name

End Sub

You should try to avoid using select. this is one of the cases where it is not recommended. Without seeing how data is structured it's difficult to propose an alternative. You can [edit](https://stackoverflow.com/posts/59504406/edit) your question and post some screenshots of sample data. — Ricardo Diaz, Dec 27 '19 at 18:24
Thank you Ricardo. I'm trying to get the data into a table so I don't have to use select in the future; I can reference the table. But I think I found the answer. I used "CurrentRegion" and there's an entire blank column. I will delete the question soon - just wanted you to see this and say thanks! — Antonio Inserni, Dec 27 '19 at 18:42
Glad you could solve it! here is [some reference](https://stackoverflow.com/a/10717999/1521579) of how to avoid select — Ricardo Diaz, Dec 27 '19 at 19:45

score 0 · Answer 1 · answered Dec 27 '19 at 19:06

This was so simply I thought about deleting the question. However, here's an answer. THe problem was I used "CurrentRegion" as the source, and CurrentRegion stops at blanks. The data contained an entire blank column. I used this source:

Create Table in Excel Worksheet using VBA

To change the code, and this worked:

  ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes).Name = Tbl_Name

Only two columns showing when creating a table with selected data

1 Answers1