Run time error '1004' VBA to create pivot table

Question

I am working on a macro to create a Pivot Table for a dynamic named range "DATA". I get a "Run-time error '1004' Method 'Range' of object'_Global' failed. From what I see on other posts it may be because I am not referencing a specific sheet? How would I correct for this?

With ActiveWorkbook.Names("DATA")
    .Name = "DATA"
    .RefersToR1C1 = _
    "=OFFSET('Closed Cases'!R1C2,0,0,COUNTA('Closed Cases'!C6),25)"
    .Comment = ""
End With
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    Range("DATA"), Version:=xlPivotTableVersion14).CreatePivotTable TableDestination:= _
    "'External Analytics!'R1C15", TableName:="PivotTable3", DefaultVersion:= _
    xlPivotTableVersion14
Sheets("External Analytics").Select
Cells(1, 1).Select
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Resolver")
    .Orientation = xlRowField
    .Position = 1
End With

Do you have "DATA" already defined as a Named range when running this code ? otherwise, it will throw an error at the first line. At what line are you getting this error ? — Shai Rado
The first with statement names the range and is not showing an error. I receive the error here: ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ Range("DATA"), Version:=xlPivotTableVersion14).CreatePivotTable TableDestination:= _ "'External Analytics!'R1C15", TableName:="PivotTable3", DefaultVersion:= _ xlPivotTableVersion14 — Ryan Hulet
That is how the macro recorder recorded naming my "data" range. It completes without error. — Ryan Hulet
@Ryan Hulet try the code in mu answer below' let me know if it works for you — Shai Rado

Shai Rado Shai Rado · Accepted Answer · 2016-11-30T18:42:14

Assuming form your post, the the Named Range "DATA" is defined correctly, the code below will create the PivotTable according to the data in "DATA" named Range, and the settings you wanted in your post.

Option Explicit

Sub DynamicCreatePivot()

Dim wsSheet             As Worksheet
Dim PvtTbl              As PivotTable
Dim PTCache             As PivotCache

' set Pivot destination sheet
Set wsSheet = ThisWorkbook.Sheets("External Analytics")

' set Pivot Cache to the data in "DATA" named range
Set PTCache = ActiveWorkbook.PivotCaches.Add(xlDatabase, "DATA")

' add this line in case the Pivot table doesn't exit >> first time running this Macro
On Error Resume Next
Set PvtTbl = wsSheet.PivotTables("PivotTable3") ' check if "PivotTable3" Pivot Table already created (in past runs of this Macro)

On Error GoTo 0
If PvtTbl Is Nothing Then ' if Pivot Table is not created

    ' create a new Pivot Table in "External Analytics" sheet, start from Cell O1 (R1C15)
    Set PvtTbl = wsSheet.PivotTables.Add(PivotCache:=PTCache, TableDestination:=wsSheet.Range("O1"), TableName:="PivotTable3")

    'Create the headings and row and column orientation
    With PvtTbl.PivotFields("Resolver")
        .Orientation = xlRowField
        .Position = 1
    End With
Else ' Pivot Table already created >> in precious code runs
    ' just refresh the Pivot cache with the updated Range (named range "DATA")
    PvtTbl.ChangePivotCache PTCache
    PvtTbl.RefreshTable
End If

End Sub

Run time error '1004' VBA to create pivot table

1 Answers