We help IT Professionals succeed at work.

Pivot table error "Run-time error '1004'

Hello,
I keep getting the following error
"Run-time error '1004'
Unable to get the PivotTables property of the worksheet class" on line
"ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array( _
        "Transaction Type", "Trading Partner Id", "Insert Date"), ColumnFields:= _
        "AS OF "
when I execute the attached code

This only happens after the run the code once.  I then get the above error on subsequent attempts.  I usually close Excel, then reopent workbook where the pattern repeats.  I am running Excel 2003
End Sub
Sub CreateReceiptsByDatePivotTable()
Dim ws_dat As Worksheet, ws As Worksheet, pt_rng As Range

Set ws_dat = ActiveWorkbook.Worksheets("by receipts-RAW")

For Each ws In ActiveWorkbook.Worksheets
    If ws.Name = "RECEIPTS BY DATE" Then
        ws.Delete
        Exit For
    End If
Next ws

Set pt_rng = ws_dat.Range(ws_dat.Range("A1"), ws_dat.Cells(ws_dat.Cells.Rows.Count, "A").End(xlUp).Offset(0, 5))
pt_rng.Cells(1, 1).Activate
'inserts Pivot Table
ActiveWorkbook.PivotTableWizard SourceType:=xlDatabase, SourceData:=pt_rng
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array( _
        "Transaction Type", "Trading Partner Id", "Insert Date"), ColumnFields:= _
        "AS OF "
ActiveSheet.PivotTables("PivotTable1").PivotFields("Count Batch ID"). _
        Orientation = xlDataField
        
ActiveSheet.Name = "RECEIPTS BY DATE"

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Rows("1:1").Select
Selection.Delete Shift:=xlUp

Range("D2").Select
ActiveWindow.FreezePanes = True

Dim lngLastRow As Long
Dim lngLastColumn As Long

lngLastRow = LastRealRow(1, ActiveSheet.Cells)
lngLastColumn = LastColByFind(ActiveSheet.Cells)
Dim rng As Range
Set rng = Cells(1, lngLastColumn)
rng.Activate
ActiveCell.Value = "Grand Total"
Set rng = Cells(2, lngLastColumn)
rng.Activate
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
    Selection.AutoFill Destination:=Range(rng, Cells(lngLastRow, lngLastColumn)), Type:=xlFillDefault
    Range(rng, Cells(lngLastRow, lngLastColumn)).Select

Range(rng, Cells(lngLastRow, lngLastColumn)).Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

MsgBox "Row: " & lngLastRow & vbTab & "Column: " & lngLastColumn


End Sub

Open in new window

Comment
Watch Question

Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
A couple of possibilities:
1) You set your pt_range to only six columns of width (Offset(0,5) in line 13 of your code.  Any chance that this is not the full width of your data?
2) If it is the full width, are the header names in the data set EXACTLY as shown in .AddFields method, including any leading or trailing spaces?

Either of the above would produce a 1004 RT error.

-Glenn
CERTIFIED EXPERT

Commented:
I have seen this when the pivot table name can't be found at run time:- look for

- something changed the name
- the pivot table sheet is not active
- you are trying to create one pivot table within another

can you post the workbook?

reg

Author

Commented:
Well, I've tried it again.  If I close the workbook and excel and then reopen the workbook and run the code, the pivot table is created.  When I run the code again, with the workbook open, I get the aforementioned error.  I think something is not being cleared - perhaps something to do with the PivotTable cache.

Author

Commented:
I experimented a little and got the code to work by replacing ActiveSheet.PivotTables("PivotTable1") with ActiveSheet.PivotTables(1).  However, I would like to understand a bit more why it is working.  I'm not to clear on why.  Is it because after running the code once, there is now a object called "PivotTable1", which conflicts with the new "PivotTable1" I am trying to create.  Whereas by using PivotTables(1) I am not refering to a pivot table by name.  I don't know.  Could anyone expound on this
ActiveWorkbook.PivotTableWizard SourceType:=xlDatabase, SourceData:=pt_rng
ActiveSheet.PivotTables(1).AddFields RowFields:=Array( _
        "Transaction Type", "Trading Partner Id", "Insert Date"), ColumnFields:= _
        "AS OF "
ActiveSheet.PivotTables(1).PivotFields("Count Batch ID"). _
        Orientation = xlDataField

Open in new window

Excel VBA Developer
CERTIFIED EXPERT
Top Expert 2014
Commented:
You're correct in your latest observation.

When you run the macro again, it updates the default PivotTable name by incrementing the value by one like so:
PivotTable1
PivotTable2
PivotTable3
....and so on.

Removing the PivotTable and re-creating a new one will assign it as the 1st PivotTable object [ActiveSheet.PivotTables (1) ], but will not reset the naming counter.

Author

Commented:
Thanks for the explanation

Explore More ContentExplore courses, solutions, and other research materials related to this topic.