"Too many different cell formats" Error After Copying and Pasting Between Spreadsheets

Recently I was working in an Excel spreadsheet and had to deal with an unusual problem. While trying to make some simple formatting changes I got an error message reading, "Too many different cell formats", and the changes did not take effect.

This is a strange problem to have because Excel 2007 and higher can accommodate up to 64,000 different cell styles. Multiple cell styles may be replicated when copying and pasting data from one spreadsheet to another. Identical styles at a 20% accent are created.

The problem of Styles getting corrupted after copying and pasting data is a fairly common one, and has been discussed on Microsoft message boards, and in the Office dev center.

A quick and easy solution to the problem is the macro posted here.

Sub StyleKiller()

Dim N As Long, i As Long

With ActiveWorkbook

N = .Styles.Count

For i = N To 1 Step -1

If Not .Styles(i).BuiltIn Then .Styles(i).Delete

Next i

End With

End Sub

Running this macro will automatically remove all of the cell styles, and the error will no longer appear when you make formatting changes.