Error: Too many cell formats

[Guest]

Howdy All

I'm receiving an error when i am copying sheets into a workbook
What happens is, we have around 200 workbooks each containing one sheet
This sheet contains a small amount of data. The only formatting on the sheet is font, font size, and borders
The macro i have, copies via paste-specialvalues the contents of each workbook to a new sheet , in one central workbook
I have also tried just copying the sheet itself
However after copying a around 20/30 sheets it errors out saying "Too many cell formats"

I'm getting frustrated as i've seen other workbooks, with hundreds of pages with formatting galor, that easily outnumbers the megar formatting i have in my workbooks

Does anyone know of a way around this problem

Thanks and cheers

AL

 

[Gord Dibben]

Al

This KB Article addresses this issue.

http://support.microsoft.com/default.aspx?scid=kb;en-us;213904

Note: article points out "about 4000" different formats as the limit and makes
suggestions for reducing.

Gord Dibben Excel MVP

 

[Guest]

I suspect that the problm is caused by a memory limitation. Try saving the workbook after doing each copy and paste.

----- AgLem wrote: -----

Howdy All,

I'm receiving an error when i am copying sheets into a workbook.
What happens is, we have around 200 workbooks each containing one sheet.
This sheet contains a small amount of data. The only formatting on the sheet is font, font size, and borders.
The macro i have, copies via paste-specialvalues the contents of each workbook to a new sheet , in one central workbook.
I have also tried just copying the sheet itself.
However after copying a around 20/30 sheets it errors out saying "Too many cell formats".

I'm getting frustrated as i've seen other workbooks, with hundreds of pages with formatting galor, that easily outnumbers the megar formatting i have in my workbooks.

Does anyone know of a way around this problem?

Thanks and cheers.

AL