Pivot table "subscript out of range" error

Question

I've recorded a macro where it creates a pivot table with data models, and it works fine with the table I created the macro with, but gives a "subscript out of range" error when I try to use it on a different table. Thus, it only seems to work if there are 168 lines, but if there's more, then it gives the error. Anyone know a simple way to modify this so that it will work with dynamic row count? Thanks a lot in advance!

Sub autopivot()

    Range("A1:H1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
        ActiveWorkbook.Connections("WorksheetConnection_Sheet1!$A$1:$H168"), Version _
        :=6).CreatePivotTable TableDestination:="Sheet2!R3C1", TableName:= _
        "PivotTable1", DefaultVersion:=6

Answer 1

Try something like this:

Sub autopivot()

    Dim wb As Workbook, ws As Worksheet, wsPivot As Worksheet, pc As PivotCache, pt As PivotTable
    Dim rngData As Range
    
    Set wb = ActiveWorkbook
    Set ws = ActiveSheet
    Set rngData = ws.Range("A1").CurrentRegion 'source data for pivot table
    
    Set wsPivot = wb.Worksheets.Add(After:=wb.Sheets(wb.Sheets.Count)) 'get reference to the added sheet
    
    Set pc = wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData)
    Set pt = pc.CreatePivotTable(tabledestination:=wsPivot.Range("A3"))
    
End Sub