Type Mismatch error

[Grant]

Hello, I get a type mismatch error with the following
code but can't see where I am going wrong. Can anyone
help please? Thanks.


For Each pvtTable In ActiveWorkbook.Worksheets _
(wkSheet.Name).PivotTables
pvtTable.PreserveFormatting = True
Worksheets(wkSheet).PivotTables _
(pvtTable).PivotFields("Line").CurrentPage = str
Next pvtTable

 

[Dick Kusleika]

Grant

Hello, I get a type mismatch error with the following
code but can't see where I am going wrong. Can anyone
help please? Thanks.


For Each pvtTable In ActiveWorkbook.Worksheets _
(wkSheet.Name).PivotTables
pvtTable.PreserveFormatting = True
Worksheets(wkSheet).PivotTables _
(pvtTable).PivotFields("Line").CurrentPage = str

pvtTable is an object and the PivotTables(x) takes a string or number. What
you want to say here is

pvTable.PivotFields("Line").CurrentPage = str

That's the whole liine.

Next pvtTable

When you use PivotTables(x), you are really using the Item property of the
PivotTables collection object. It's the same as saying

PivotTables.Item(x)

Item is the default property for collection objects, so you don't have to
type it.

 

[Grant]

This causes a method 'Current Page' of object PivotField
failed error.

 

[Tom Ogilvy]

For Each pvtTable In ActiveWorkbook.Worksheets _
(wkSheet.Name).PivotTables
pvtTable.PreserveFormatting = True
pvtTable.PivotFields("Line").CurrentPage = str
Next pvtTable

if the above is what you tried, perhaps str is not a valid selection. str
is actually a function in vba so you might want to use a different name like

sStr

 

[Grant]

changed to sStr and still failed with same error.

 

[Grant]

This is really strange. I can get the following code to
work:
ActiveSheet.PivotTables("PvtFTE").PivotFields
("Line").CurrentPage = sStr


but not:

For Each pvtTable In ActiveWorkbook.Worksheets
(wkSheet.Name).PivotTables
With pvtTable
.PreserveFormatting = True
.PivotFields("Line").CurrentPage = sStr
End With
Next pvtTable


Any ideas?
Thanks,
Grant.

 

[Dick Kusleika]

Grant

How many pivottables are on the sheet? In the Immediate window type

?Sheet1.PivotTables.Count

where sheet1 is the codename for the sheet in question. Do that even if you
think you know how many there are. It has to be that there is no Line pivot
field or that the Line pivot field is not a page field for one of the
pivottables on the sheet.