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

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

May 15, 2019

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. 

 

Please reload

Contact Me With Your Litigation Support Questions:

seankevinoshea@hotmail.com

  • Twitter Long Shadow

© 2015 by Sean O'Shea . Proudly created with Wix.com