We help IT Professionals succeed at work.
Get Started

Pivot table error "Run-time error '1004'

444 Views
Last Modified: 2012-06-21
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
US Data Team Lead
CERTIFIED EXPERT
Top Expert 2014
Commented:
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE