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

Grant


"Grant" wrote in message
...
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.


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

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



-----Original Message-----
Grant


"Grant" wrote in
message
...
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.


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com



.

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

--
Regards,
Tom Ogilvy


"Grant" wrote in message
...
This causes a method 'Current Page' of object PivotField
failed error.



-----Original Message-----
Grant


"Grant" wrote in
message
...
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.


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com



.

changed to sStr and still failed with same error.


-----Original Message-----
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

--
Regards,
Tom Ogilvy


"Grant" wrote in
message
...
This causes a method 'Current Page' of object
PivotField
failed error.



-----Original Message-----
Grant


"Grant" wrote in
message
...
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.


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com



.



.

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.


-----Original Message-----
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

.

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.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Grant" wrote in message
...
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.


-----Original Message-----
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

.