Pivot Table Macro Run-time error
 

This macro worked in Excel2003 but fails in 2007. It was designed to create
a pivot chart from a large (12,500 rows x 14 columns) spreadsheet listing the
concentrations of 150 chemical parameters in 8 wells over 28 sample periods.

Instead of creating the chart with 150 series lines on it, I had it show
only 3 by repeating the line

..PivotItems("2-Butanone").Visible = False

147 times with a different chemical in quotes each time.

The problem seems to be that Excel 2007 can only handle 119 repetitions of
this command because every time the macro is executed it results in

€œRun-time error €˜-2147417848 (80010108)
Method €˜visible of object €˜PivotItem failed€�

I have two questions:

1) Is there a more elegant way to create a pivot chart that has the ability
to show any (or all) of the 150 chemicals but is initially drawn with only a
few?
2) Has anyone else found that Excel 2007 is generally slower, less robust
and more prone to problems than Excel 2003?

Thank you for any help you can provide. If you think code for the entire
might help, Ill be glad to provide it.

Pivot Table Macro Run-time error
 

IIRC, you need to have at least one pivot item visible or it gives this error
(haven't tested it today). Might that be the issue?
--
HTH,

Barb Reinhardt



"Catenary" wrote:

This macro worked in Excel2003 but fails in 2007. It was designed to create
a pivot chart from a large (12,500 rows x 14 columns) spreadsheet listing the
concentrations of 150 chemical parameters in 8 wells over 28 sample periods.

Instead of creating the chart with 150 series lines on it, I had it show
only 3 by repeating the line

.PivotItems("2-Butanone").Visible = False

147 times with a different chemical in quotes each time.

The problem seems to be that Excel 2007 can only handle 119 repetitions of
this command because every time the macro is executed it results in

€œRun-time error €˜-2147417848 (80010108)
Method €˜visible of object €˜PivotItem failed€�

I have two questions:

1) Is there a more elegant way to create a pivot chart that has the ability
to show any (or all) of the 150 chemicals but is initially drawn with only a
few?
2) Has anyone else found that Excel 2007 is generally slower, less robust
and more prone to problems than Excel 2003?

Thank you for any help you can provide. If you think code for the entire
might help, Ill be glad to provide it.

Pivot Table Macro Run-time error
 

Hi

Send me a copy of the workbook, and I'll take a look.
To mail direct, send to
roger at technology4u dot co dot uk
Remove the at and dots to make a valid email address.
--
Regards
Roger Govier

Catenary wrote:
This macro worked in Excel2003 but fails in 2007. It was designed to create
a pivot chart from a large (12,500 rows x 14 columns) spreadsheet listing the
concentrations of 150 chemical parameters in 8 wells over 28 sample periods.

Instead of creating the chart with 150 series lines on it, I had it show
only 3 by repeating the line

.PivotItems("2-Butanone").Visible = False

147 times with a different chemical in quotes each time.

The problem seems to be that Excel 2007 can only handle 119 repetitions of
this command because every time the macro is executed it results in

€œRun-time error €˜-2147417848 (80010108)
Method €˜visible of object €˜PivotItem failed€�

I have two questions:

1) Is there a more elegant way to create a pivot chart that has the ability
to show any (or all) of the 150 chemicals but is initially drawn with only a
few?
2) Has anyone else found that Excel 2007 is generally slower, less robust
and more prone to problems than Excel 2003?

Thank you for any help you can provide. If you think code for the entire
might help, Ill be glad to provide it.

Pivot Table Macro Run-time error
 

I have left three series displayed, so that shouldn't be the problem.

"Barb Reinhardt" wrote:

IIRC, you need to have at least one pivot item visible or it gives this error
(haven't tested it today). Might that be the issue?
--
HTH,

Barb Reinhardt



"Catenary" wrote:

This macro worked in Excel2003 but fails in 2007. It was designed to create
a pivot chart from a large (12,500 rows x 14 columns) spreadsheet listing the
concentrations of 150 chemical parameters in 8 wells over 28 sample periods.

Instead of creating the chart with 150 series lines on it, I had it show
only 3 by repeating the line

.PivotItems("2-Butanone").Visible = False

147 times with a different chemical in quotes each time.

The problem seems to be that Excel 2007 can only handle 119 repetitions of
this command because every time the macro is executed it results in

€œRun-time error €˜-2147417848 (80010108)
Method €˜visible of object €˜PivotItem failed€�

I have two questions:

1) Is there a more elegant way to create a pivot chart that has the ability
to show any (or all) of the 150 chemicals but is initially drawn with only a
few?
2) Has anyone else found that Excel 2007 is generally slower, less robust
and more prone to problems than Excel 2003?

Thank you for any help you can provide. If you think code for the entire
might help, Ill be glad to provide it.