Cannot create table VBA excel

Question

When I run this function

    Call makeTable("Uber Tracker", range(Cells(7, 1), Cells(LastUber, 42)))
    Call makeTable("Finance Tracker", range(Cells(21, 1), Cells(LastFinance, 23)))

The first call works fine, but the second shows error 1004 worksheet range must be on the same sheet as the table being created.

    Sub makeTable(TableSheet As String, TableRange As range)
        On Error GoTo errorHandler
        Sheets(TableSheet).ListObjects.Add(xlSrcRange, TableRange, ,xlYes).name = TableSheet

    errorHandler:
        If Err.Number <> 0 Then
            Debug.Print "Error number :" + CStr(Err.Number) + " reason: " + Err.Description
        End If

    End Sub

What is wrong? I have checked which sheet it is selecting, and it should be correct, same for the range.

vacip · Accepted Answer · 2015-12-23 11:24:05Z

2

Your TableRange is on the ActiveSheet(wherever that is). You are trying to create a table on sheet ("Finance Tracker") with a range taken from the activesheet.

Depending on what you really need, you can either change the maketable sub like this:

ActiveSheet.ListObjects.Add(xlSrcRange, TableRange, ,xlYes).name = TableSheet

Or change the call like this:

Call makeTable("Finance Tracker", sheets("Finance Tracker").range(sheet("Finance Tracker").Cells(21, 1), sheet("Finance Tracker").Cells(LastFinance, 23)))

Just make sure everything is happening on the same sheet.

EDIT:

One more thing to try (works for me this way aswell):

Worksheets("Uber Tracker").Activate
Call makeTable("Uber Tracker", Range(Cells(7, 1), Cells(10, 42)))
Worksheets("Finance Tracker").Activate
Call makeTable("Finance Tracker", Range(Cells(21, 1), Cells(30, 23)))

Just make sure the Range you use to create a table is on the same sheet where the Table is being added. There are many ways to do this, these are just a few examples.

edited Dec 23, 2015 at 11:24

answered Dec 23, 2015 at 10:27

vacip

5,4563 gold badges29 silver badges56 bronze badges

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

6 Comments

user1902692 Over a year ago

so you're saying if I explicitly associate the range with the desired sheet, then it should work?

user1902692 Over a year ago

tried your activesheet solution, now they both don't work

vacip Over a year ago

mmm... give me a minute. in the mean time, does the other method work?

vacip Over a year ago

Hmm, the Activesheet version works for me. The thing is that the Range you feed to the Table adding method must come from the sheet where you are putting the Table.

user1902692 Over a year ago

your edit worked, though it's weird because I have tried to activate the worksheet from within the function and that doesn't seem to work

|

Rory · Accepted Answer · 2015-12-23 10:58:01Z

0

You could also just use the address of whichever range is passed to the routine:

Sheets(TableSheet).ListObjects.Add(xlSrcRange, Sheets(TableSheet).Range(TableRange.Address), ,xlYes).name = TableSheet

answered Dec 23, 2015 at 10:58

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Collectives™ on Stack Overflow

Cannot create table VBA excel

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related