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.

 

[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("PivotTable").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]

That would indicate that the Item "Flash" does not exist in the PivotField "Period". This seem highly likely, since I would expect the field Period to contain dates.

 

[kht0307]

Thanks Alex, it does exist in the PivotField "Period", see below screenshot:

 

[Alex Blakenburg]

Can you type Flash into a cell eg d5 and then in e5 put =D5= and point to the cell that contains Flash ?
or simply ="Flash" = the cell that contains Flash

 

[kht0307]

Yes, see below:

So in cell B29 I have typed = B26 and the result is Flash

 

[Alex Blakenburg]

That won't show you if its exactly Flash so either
="Flash" = B26
or =Len(B26)
if the first one returns false definitely do the 2nd one too

 

[kht0307]

Added extra formulae to check:

 

[Alex Blakenburg]

I edited my post did you use B26 in those 2 formulas.

 

[kht0307]

I edited my post did you use B26 in those 2 formulas.

Yes