asked on

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

Glenn Ray

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

regmigrant

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

Juan Velasquez

ASKER

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.

Juan Velasquez

ASKER

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

ASKER CERTIFIED SOLUTION

Glenn Ray

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Juan Velasquez

ASKER

Thanks for the explanation

Pivot table error &quot;Run-time error '1004'

Pivot table error "Run-time error '1004'