Error 1004

[sumnesh]

Hi ,

Any help is appreciated. It was working fine on another Data set and all i
did is to change the List values and now I am getting this error :

Run Time Error '1004'

Unable to Set _Default property on PivotItem class

background:

I am running a loop where it looks at a list of values on another worksheet
and Pivot Tables Runs through the loop and copies and paste the Pivot Result
Set in another worksheet and Goes to the next Value in the list and so on.

here is my Code:


Dim pvtField As PivotField
Dim iCount As Integer


'SM add
pastefromstartcolumn = 1
pastefromendcolumn = 123
pastefromstartrow = 5
pastefromendrow = Cells(65536, 1).End(xlUp).Row


'SM quick add- need to define this b/f loop starts
'defining as zero, b/c it will be established as +1 every time
pastetostartrow = 1
Worksheets("Pasted Results").Activate
Cells.Delete
Worksheets("Ranking Incl ZZ").Activate
'paste headers first
Range(Cells(4, pastefromstartcolumn), Cells(4, pastefromendcolumn)).Copy
Worksheets("Pasted Results").Activate
Cells(pastetostartrow, 1).Select
Selection.PasteSpecial xlValues
Worksheets("Ranking Incl ZZ").Activate


Set pvtTable = Worksheets("Ranking Incl ZZ").PivotTables("PivotTable1")
Set pvtField = pvtTable.PivotFields("AA_name")

For iCount = 0 To 101

pvtField.CurrentPage = _
Worksheets("AAList").Range("AA_List").Offset(iCount, 0).Value

pastefromstartrow = 5
pastefromendrow = Cells(65536, 1).End(xlUp).Row

'SM add
'now the loop
For celltopaste = pastefromstartrow To pastefromendrow
'aa copy
Cells(1, 2).Copy Destination:=Worksheets("Pasted
Results").Cells(pastetostartrow + 1, 1)
'selecting the results of the pivottable (and the two ranking columns to the
right of it
Range(Cells(celltopaste, pastefromstartcolumn), Cells(celltopaste,
pastefromendcolumn)).Copy
Worksheets("Pasted Results").Activate
Cells(pastetostartrow + 1, 2).Select
Selection.PasteSpecial xlValues
Worksheets("Ranking Incl ZZ").Activate
'this variable will change after every paste
pastetostartrow = Worksheets("Pasted Results").Cells(65536, 1).End(xlUp).Row
Next celltopaste



Next iCount
Response = MsgBox("Task Complete")
End Sub

 

[Per Jessen]

Which line is causing the error ?

Click 'Debug' and see which line is highlighted.

....
Per

 

[sumnesh]

Hi Jessen

This is is line highlighted:


pvtField.CurrentPage = _
Worksheets("AAList").Range("AA_List").Offset(iCount, 0).Value

Thanks for looking into this.

 

[Per Jessen]

I somehow expected this. A few things to do:

- What is the value of the iCount variable (place the mouse pointer over
the variable). Maybe you shouldn't loop to 101.
- What is the value of
'Worksheets("AAList").Range("AA_List").Offset(iCount, 0).Value'. If <> ""
then check if it is found in your current data set.

Hopes this helps.
....
Per