create a repeatable pivot table that loads on the same worksheet

Question

I try to take a table and create a pivot table adjacent to it.

This macro needs to work on separate worksheets so the table and pivot table names need to be generic and I am having a little trouble creating those names and I keep receiving errors.

Sub Macro1()
    Dim rawtable As TableObject
    Dim Number_of_producers_appointed As Sheet1
    Dim Ptable As PivotTable
    Dim tabledata As DataTable

    Range("H1").Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$H$697"), , xlYes).Name = _
        "rawtable"
    Range("rawtable").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "tabledata", Version:=6).CreatePivotTable TableDestination:= _
        Number_of_producers_appointed & "!R6C10", TableName:="Ptable", _
        DefaultVersion:=6
    Sheets("Number of producers appointed").Select
    Cells(6, 10).Select
    With ActiveSheet.PivotTables("Ptable").PivotFields("Producer Type")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Ptable").PivotFields("Producer Type")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("Ptable").AddDataField ActiveSheet.PivotTables( _
        "Ptable").PivotFields("EPN"), "Count of EPN", xlCount
End Sub

The error I am receiving is

Error 91; object variable or With block variable not set

on:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "tabledata", Version:=6).CreatePivotTable TableDestination:= _
    Number_of_producers_appointed & "!R6C10", TableName:="Ptable", _
    DefaultVersion:=6

Answer 1

I suggest to build everything object-by-object like this:

Sub GenerateNewPivottable()
    Dim wsProducer As Worksheet
    Dim objRawData As ListObject
    Dim lastUsedRow As Long
    Dim objPivotCache As PivotCache
    Dim objPivotTable As PivotTable

    ' define a variable for your worksheet
    Set wsProducer = ActiveWorkbook.Worksheets("Number of producers appointed")

    ' get the last used row in its column A
    lastUsedRow = wsProducer.Cells(wsProducer.Rows.Count, "A").End(xlUp).Row

    ' If you want to convert an existing listobject ("table") to a range before:
    'For Each objRawData In wsProducer.ListObjects
    '    objRawData.Unlist
    'Next objRawData

    ' convert the used range of the worksheet to a new listobject
    Set objRawData = wsProducer.ListObjects.Add( _
        SourceType:=xlSrcRange, _
        Source:=wsProducer.Range("A1:H" & lastUsedRow), _
        XlListObjectHasHeaders:=xlYes)

    ' Give the listobject a name (not necessary if default name is okay)
    'objRawData.Name = "rawtable"

    ' use listobject for a new pivotcache
    Set objPivotCache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=objRawData)

    ' delete existing pivottables on this sheet if necessary
    'For Each objPivotTable In wsProducer.PivotTables
    '    objPivotTable.TableRange2.Clear
    'Next objPivotTable

    ' generate a new pivottable with above pivotcache
    Set objPivotTable = objPivotCache.CreatePivotTable( _
        TableDestination:=wsProducer.Cells(6, 10))

    ' Give the pivot table a name, not necessary if default is okay
    'objPivotTable.Name = "Ptable"

    ' Define its row fields, column fields and data fields:
    With objPivotTable.PivotFields("Producer Type")
        .Orientation = xlRowField
        .Position = 1
    End With
    With objPivotTable.PivotFields("EPN")
        .Orientation = xlDataField
        .Function = xlCount
        .Name = "Count of EPN"
    End With
End Sub

You may address a worksheet by its name or its index:

Set wsProducer = ActiveWorkbook.Worksheets("Number of producers appointed")
Set wsProducer = ActiveWorkbook.Worksheets(5)