Run Time Error '1004'
 

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(iCoun t, 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

Run Time Error '1004'
 

Any help is appreciated? Did i post in the wrong section?

"sumnesh" wrote:

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(iCoun t, 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