Copy Paste failed in excel file migrated from Excel 2003 to Excel

[Handong Chen]

We have two excel files (~15 sheets each, 20-60 rows and ~30 columns each
sheet) that has been working well in 2003. Recently, we upgraded our office
to 2007. And saved these files to xlsm format. We open these two files in two
different Excel instances. When we tried to copy cells from one workbook to
the other, the destination cells become empty. If we use CTRL+Z to undo, a
msgbox pops out that says "Too many cells formats". BTW, the contents to be
copied has merged cells.

We tried to us VBA to copy all the formulas and formats of these two files
to two new files (No format was copied). The same problem happens when we try
to copy between these two files.

Please help.

Thanks.

 

[Handong Chen]

I solved this issue myself. After the conversion from Excel 2003 to Excel
2007, there are 58777 styles in the workbook! The following code can delete
extra styles generated during the conversion from Excel 2003 to Excel 2007.
What this code does is to delete those styles whose names end with a digit.



Sub ClearStyles()
Dim i&, Cell As Range, RangeOfStyles As Range
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
'Add a temporary sheet
Set ws = Sheets.Add(before:=Sheets(1))
'List all the styles

For i = 1 To ActiveWorkbook.Styles.Count
ws.Cells(i, 1) = ActiveWorkbook.Styles(i).Name
Next

Cells(1, 3).Formula = "=ISNUMBER(INT(RIGHT(A1,1)))"
RowCount = Cells(1, 1).End(xlDown).Row

Cells(1, 3).AutoFill Destination:=Range(Cells(1, 3), Cells(RowCount, 3))

Set RangeOfStyles = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
Application.CalculateFull

For Each Cell In RangeOfStyles
'If Not Cell.Text Like "Normal" Then

If Cell.Offset(0, 2) Then
On Error Resume Next
ActiveWorkbook.Styles(Cell.Text).Delete
'ActiveWorkbook.Styles(Cell.NumberFormat).Delete
End If
Next Cell
'delete the temp sheet
Application.DisplayAlerts = False
ActiveSheet.Delete

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub