Convert Entire Sheet to a Named Table for a Pivot Table

[default_name]

Hi guys,

I have looked online to try and find some solutions but I keep getting an error.
Perhaps I am messing up with the syntax.

I want the vba to go to the worksheet titled "Index", and then to convert all of the data on that sheet into a table called "Source"
(similar to going to the "Index" sheet, hitting control+A, and setting the range as a table called "Source")

Here is what I have right now:

    Sheets("Index").Select
    Range("A1").Select
    Dim tbl As ListObject
    Dim rng As Range
    Set rng = Range(Range("A1"), Range("A1").SpecialCells(xlLastCell))
    Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes).Name _
        = "Source"

What am I doing wrong here?

The reason I am doing this is that I want to build a pivot table that references all of the data on the Index worksheet.
Previously, the pivot table was based off of a large range on the Index worksheet, but since the amount of data on the Index sheet varies, the pivot table occasionally ends up with (blank) rows.
I figure that if I convert all of the data on the Index sheet to being a table, that I can simply reference that table name ("Source") (instead of a set range) for the pivot table (this worked when I did it manually).
If there is a more simple method that wouldn't require me to convert to a table, I would be open to ideas.

Thanks

 

[Fluff]

How about

Sub defaultname()
   With Sheets("Index").Range("A1").CurrentRegion
      .Parent.ListObjects.Add(xlSrcRange, Range(.Address(, , , True)), , xlYes).Name = "Source"
   End With
End Sub

 

[default_name]

How about

Sub defaultname()
   With Sheets("Index").Range("A1").CurrentRegion
      .Parent.ListObjects.Add(xlSrcRange, Range(.Address(, , , True)), , xlYes).Name = "Source"
   End With
End Sub

Works perfectly! Thanks Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.