Create a pivot table in a predefined location

Question

I'm trying to create a pivot table and place it in a predefined location (not a new sheet).

Before running the macro each time, the pivot table is deleted and also the predefined sheet.

I noticed that when you create a table manually, the table name increases by one each time (PivotTable2, PivotTable3...), which I think is where my code is falling down.

I get a Run-time error 5, invalid procedure call or argument on this line:

ActiveWorkbook.PivotCaches.Create

I did check out this thread, which says that you can remove the table name parameter completely, or rename it - however I still get errors.

My code:

Sub CreatePivot()'
' CreatePivot Macro
'

' Set data as table
    Sheets("Filtered Flags").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$G$16000"), , xlYes).Name _
        = "Table1"

' Create worksheet for pivot output
    Sheets.Add Before:=ActiveSheet
    ActiveSheet.Name = "Flag Pivot"

'Create Pivot Table
    Sheets("Filtered Flags").Select
    Range("Table1[[#Headers],[Order '#]]").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Table1", Version:=6).CreatePivotTable TableDestination:="Flag Pivot!R3C1" _
        , TableName:="PivotTable5", DefaultVersion:=6
    Sheets("Flag Pivot").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Material #")
        .Orientation = xlRowField
        .Position = 1
    End With

End Sub

score 0 · Answer 1 · answered Apr 26 '18 at 14:01

So there's a few things to work with here. One of the basics to get started with (and to understand why some of my example below is constructed) is to learn about avoiding the use of Select and Activate. Also, always using Option Explicit will help you sidestep many problems in naming and using variables.

Option Explicit

Public Sub CreatePivot()
    '--- establish some basic objects to use...
    Dim flagsWS As Worksheet
    Set flagsWS = ThisWorkbook.Sheets("Filtered Flags")

    '--- now, define the range of cells that contain the data
    '    and create the pivot cache
    With flagsWS
        Dim lastRow As Long
        Dim lastCol As Long
        Dim dataArea As Range
        Dim dataAreaString As String
        lastRow = .Cells(.Cells.rows.count, 1).End(xlUp).Row
        lastCol = .Cells(1, .Cells.Columns.count).End(xlToLeft).Column
        Set dataArea = .Range(.Cells(1, 1), .Cells(lastRow, lastCol))
        dataAreaString = .Name & "!" & dataArea.Address

        Dim pCache As PivotCache
        Set pCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                                     SourceData:=dataAreaString)
    End With

    '--- create or clear the sheet for our new pivot
    Dim pivotWS As Worksheet
    On Error Resume Next
    Set pivotWS = ThisWorkbook.Sheets("Flag Pivot")
    On Error GoTo 0
    If Not pivotWS Is Nothing Then
        '--- delete everything on the worksheet so we're starting clean
        pivotWS.Cells.Clear
    Else
        Set pivotWS = ThisWorkbook.Sheets.Add
        pivotWS.Name = "Flag Pivot"
    End If

    '--- finally create the pivot table
    Dim flagPT As PivotTable
    Set flagPT = pivotWS.PivotTables.Add(PivotCache:=pCache, _
                                              TableDestination:=pivotWS.Range("A4"), _
                                              TableName:="AnnasPivotTable")

End Sub

Create a pivot table in a predefined location

1 Answers1