Error creating table in VBA Excel

Question

So I am importing a bunch of data into a sheet and then I want to make it into a table. I do not know how many rows of data there will be. This code was working correctly a few days ago, but I suppose I might have unwittingly changed something. Here is the code:

Sheets("Enrollment Data Aggregate").ListObjects.Add(xlSrcRange, Range([A1].End(xlDown), "$n$1"), , xlYes).Name = "EDA_Table"

I am getting an error message that says "The worksheet data for a table needs to be on the same sheet as the table." As far as I know, I am making the table on the same page as where the data is. Any suggestions?

Just a friendly tip, you may want to read over this page: The How-To-Ask Guide so you can always be sure that your questions are easily answerable and as clear as possible. Be sure to include any efforts you've made to fix the problem you're having, and what happened when you attempted those fixes. Also don't forget to your show code and any error messages! — Matt C
– Matt C, Commented Nov 2, 2016 at 18:07

Niclas · Accepted Answer · 2016-11-02 19:24:22Z

1

Try this. You need to have a range object declared, so that you can access its properties.

Dim wks As Worksheet
Set wks = ThisWorkbook.Sheets("Enrollment Data Aggregate")
Dim rng As Range
Set rng = wks.Range([A1].End(xlDown), "$n$1")
wks.ListObjects.Add(xlSrcRange, rng, , xlYes).Name = "EDA_Table"

edited Nov 2, 2016 at 19:24

answered Nov 2, 2016 at 17:04

Niclas

1,3024 gold badges19 silver badges35 bronze badges

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

5 Comments

Lui Over a year ago

So, now it gives me an error and highlights the fourth line. It says "Method 'Range' of object '_Worksheet' failed".

Niclas Over a year ago

Are you sure you are looking at the newest edition? Refresh the page and try again :-)

Lui Over a year ago

Everything is working! Thank you! I also looked and found out that I had a named range that might have been messing things up. I'm not sure, but I didn't need it so I deleted it. I ran the macro again and everything worked. Thank you! And for some reason I can't mark your answer as accepted. A box shows up that says an error has occurred. I think it has something to do with reputation points?

Niclas Over a year ago

Glad it worked. I guess you can try again later, should be possible for everyone to accept an answer :-)

Lui Over a year ago

I will do that later when I can get it to work! Thanks!

Paxz · Accepted Answer · 2018-07-17 13:42:31Z

0

Make sure the Function/Sub/Code, that converts your Range to Table, is put in the same sheet in which you intend to have the table on the VBA Project window.

edited Jul 17, 2018 at 13:42

Paxz

3,0661 gold badge22 silver badges36 bronze badges

answered Jul 17, 2018 at 7:43

ExcelUser

1

Comments

entreprenerds · Accepted Answer · 2019-10-29 06:39:54Z

0

I believe you can also solve the bug by specifying the range in the table.

For example, changing:

Sheets("Enrollment Data Aggregate").ListObjects.Add(xlSrcRange, Range([A1].End(xlDown), "$n$1"), , xlYes).Name = "EDA_Table"

To:

Sheets("Enrollment Data Aggregate").ListObjects.Add(xlSrcRange, Sheets("Enrollment Data Aggregate").Range([A1].End(xlDown), "$n$1"), , xlYes).Name = "EDA_Table"

edited Oct 29, 2019 at 6:39

entreprenerds

1,04713 silver badges22 bronze badges

answered Oct 29, 2019 at 1:17

Moyassar Eltigani

1

Collectives™ on Stack Overflow

Error creating table in VBA Excel

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related