Run Time Error 1004 in Loop

S

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run Time Error '1004' 1
Error 1004 3
1004 Run-Time Error 3
Run-time error 1004 8
browser gives run time 1004 error 0
Run-time error 1004 9
Search other sheets for numbers & color them 10
Need help-For loop 3

Top