Type Mismatch error

G

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
 
D

Dick Kusleika

Grant


Grant said:
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
Click to expand...

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
Click to expand...

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.
 
T

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
 
G

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.
 
D

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.
 
Top