Pivot Table Macro Run-time error

[Catenary]

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, I’ll be glad to provide it.

 

[Barb Reinhardt]

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?

 

[Roger Govier]

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.

 

[Catenary]

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