Runtime error "1004" Unable to set the Position property of the PivotItem Class

[kht0307]

Hi, I have the following code, that results in the above error when it hits the highlighted line:

For Each WS In ActiveWorkbook.Worksheets
WS.Select
If ActiveSheet.Name Like "Pivot*" And ActiveSheet.Name <> "Pivot Current month" Then
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Period")
.PivotItems("Flash").Position = .PivotItems.Count
End With
End If
Next WS


Can anyone please advise me as to what is going wrong here.

 

[Alex Blakenburg]

Do you know how to view the immediate window ? Ctrl+G if its not visible.
What do you get if you add the blue lines below:
(it will probably error out on the 2nd one. Check that the worksheet name and pivot sheet name makes sense)

Sub test()

For Each ws In ActiveWorkbook.Worksheets
    ws.Select
    If ActiveSheet.Name Like "Pivot*" And ActiveSheet.Name <> "Pivot Current month" Then

        With ActiveSheet.PivotTables("PivotTable1").PivotFields("Period")
                Debug.Print ws.Name, ActiveSheet.PivotTables(1).Name
                Debug.Print .PivotItems("Flash").Name
        .PivotItems("Flash").Position = .PivotItems.Count
        End With
    End If
Next ws
End Sub

 

[kht0307]

This is what I get in the immediate window when the above debug code runs:

 

[Alex Blakenburg]

OK. You have a filter applied to Period.
It looks like " .PivotItems.Count" is counting ignoring the filter but then when it uses that position it decides its not valid since its greater than the last visible position.

To test it take the filter off your Period column and run the code.

 

[kht0307]

Perfect! Many thanks for the solution

 

[Alex Blakenburg]

You're welcome. Glad I could help.