Seemingly random error due to conditional formatting

[gallen]

I've been investigating this problem for over a day now and it's infuriating.

I have a 'Summary' sheet that performs a loop whereby it it opens a workbook, copies a small range of cells and pastes them into the summary.

I made some alterations and now Excel crashes on the 2nd loop or 3rd (seemingly random) at the point of closing the workbook.

When excel restarts it gets the recovered file and explains errors were found. I opened the log and it had this:

<!--?xml version="1.0" encoding="UTF-8" standalone="yes"?-->
<recoverylog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logfilename>error019320_01.xml</logfilename><summary>Errors were detected in file 'C:\Users\ntgall\AppData\Roaming\Microsoft\Excel\160523_PT_RUNPLAN (version 1).xlsb'</summary><additionalinfo><info>One or more invalid conditional formats were removed from the workbook</info></additionalinfo></recoverylog>

So it seems I've added a conditional format that it doesn't like. However these 3 workbooks have all been created from the same template. How would a conditional format cause a crash?

The procedure is below and it crashes on the ImportFrom.Close line

Has anyone had this before?

Private Sub ImportPlans()    '****************************************************************************************************************
    'Sub to open all plans with same Week Commencing date as the one on the sheet and import them into this workbook*
    '****************************************************************************************************************
    
On Error GoTo errHandle


Dim Summary As Worksheet
Dim ImportFrom As Workbook 'Runplan to import
Dim sFileLoc As String 'location of File to open
Dim sUnit As String
Dim sYear As String
Dim sFileName As String
Dim iResponse As Integer


Dim i As Integer
Dim iPlanCount As Integer
Dim iRow As Integer
Dim bAlreadyOpen As Boolean
        
    iRow = 3 'starting row for first plan
    i = 1
    
    'Check if any data currently exists
    If cmdPrint1.Visible = True Then
        iResponse = MsgBox("Overwrite current data?", vbQuestion + vbYesNoCancel, "Overwrite?")
        If iResponse = vbNo Or iResponse = vbCancel Then Exit Sub
    End If
    
    'disable  screenupdating, events and displayalerts
    Enable False
    'delete any data
    ClearSheet
    
    Do Until Sheet3.Range("_Unit").Offset(i) = ""
        sUnit = Sheet3.Range("_Unit").Offset(i).Value
        
        sYear = GetYear(Range("_WC"))
        sFileName = GetFileName(sUnit)
        
        sFileLoc = Sheet3.Range("_Unit").Offset(i, -1).Value & sYear & sFileName
        If CheckFileExists(sFileLoc) = False Then
            MsgBox "Error finding Unit: " & sUnit & "'s plan. Please check file exists and is in correct location.", vbCritical, "Can't find file for this week"
            Enable True
            i = i + 1
           GoTo skipImport
        End If
        
        'open the workbook with plan to import
        If FileIsOpen(sFileName) Then
            Set ImportFrom = Workbooks(sFileName)
            bAlreadyOpen = True
        Else
            Set ImportFrom = Workbooks.Open(sFileLoc, True, True)
            bAlreadyOpen = False
        End If
       
        ImportFrom.Sheets(1).Range("Print_Area").Copy
        Sheet2.Range("A" & iRow).PasteSpecial xlPasteAll
        
        'if sheet was open before import, don't close it.
        If bAlreadyOpen = False Then
            ImportFrom.Saved = True
            ImportFrom.Close
        End If


        i = i + 1
        iRow = iRow + PlanHeight
        iPlanCount = iPlanCount + 1
skipImport:


    Loop
    
    ShowPrintButtons iPlanCount
    Range("A1").Select
    Enable True
    Application.DisplayAlerts = True
    
Exit Sub


errHandle:
    MsgBox Err.Description
    Application.DisplayAlerts = True
    Enable True
End Sub

 

[gallen]

I've sorted it!

I'd copied and pasted some cells into a 'Test Data' sheet to save me having to keep manually entering test data. However, the conditional formatting on this sheet is what was causing all the headaches. Not sure why, but removing all conditional formatting from this sheet fixed the error. Luckily I didn't have to remove any conditional formatting from the more important sheets.

I'm guessing the copied and pasted formatting was the issue.