"Too Many Cell Formats" when merging files

[Chad Ginther]

I am having trouble addressing a problem with the dreaded "Too Many Cell
Formats" error, when I exceed the 4000 style limit.

I am merging multiple workbooks together, which all come from the same
template. Thus they all have the same formatting. But when I merge them
together into one book, using the Sheet.Copy command in VBA, I end up running
into too many cell formats. It seems that the number of duplicate styles is
very high. I've tried cleaning up the template to reduce the number of
formats. That helped for a while, until I started adding more sheets, which
multiplied the number of duplicate styles.

Only a handful seem to be named styles, listed on the Styles list as
"Normal_Sheet1", "Normal_Sheet2" and so on. But there are thousands more
cell formats throughout the workbook that I cannot detect.

Is there any better way to copy sheets without bringing all of the styles,
or a way to merge the duplicate styles together as I go along?

 

[gimme_this_gimme_that]

If the first rows of the two worksheets are headers you could use ADO
and a select statement to merge the two Worksheets.

No formatting to worry about.

Read the thread : "How do you store data in Excel as a database?" for
examples.