CODEWITHSUNDEEP
vbaexcel
user1902692
user1902692

Reputation: 37

Cannot create table VBA excel

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.

Upvotes: 0

Views: 1453

Answers (2)

vacip
vacip

Reputation: 5406

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.

Upvotes: 2

Rory
Rory

Reputation: 34045

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

Upvotes: 0

Related Questions