Error copying worksheet

[Guest]

Hi,
I have been given two workbooks to merge each with about 10 worksheets
containing about 5 graphs each.
When I try to move a worksheet from one workbook into the other I get this
error message:
No more new fonts may be applied in this workbook.
Does anyone out there have any ideas what to do?
I have tried going to format and styles but can't seem to get past it.
When I receive the message I can then only close it by going to task manager
and ending the programme that way.
Hope someone out there can help me.
Thanks.

 

[Stephane Rodriguez]

There are "only" 4096 styles available for an entire workbook. Each time a
cell has a specific border option, font, alignment, a style is created
internally to store the info. The best you can do, before the error occurs,
is to streamline the amount of styles you are using by editing a couple of
the worksheets, making range selections and changing styles to entire areas.
In hope that it will be enough streamlining that you never cross the limit.

 

[Guest]

Thanks for the reply Stephane.
I tried clearing all the styles etc. but still no joy unfortunately.
I think the the fact that it had lots of pie charts might have contributed
to this problem.
Also the person that created it had gone in and done some formatting on the
pie chart so maybe that was it.
Thanks again!

 

[Stephane Rodriguez]

Formatting in charts are either totally embedded in the chart itself and
have no inherent limit, or refer to fonts that are globally defined in the
workbook and are limited in number.

Font formatting in charts can indeed produce too many. Here is how to reduce
it : that may take substantial time to do it by hand, you can edit fonts in
the following places, click the font tab in each and uncheck "Auto scale".
The places are : chart title, legend entries, axis, any data label. For each
chart. It may be done with a VBA code : 1) record a macro where you edit the
font formatting of a chart. 2) generalize the macro and put it in a loop
where you iterate through the entire Chart collection of each worksheet.
Then iterate through all worksheets of the workbook.

What's unfortunate is that there is no way to know how many styles/fonts are
being used, so you never know how far you are from the 4096 limit.