Compile Error : Out of Memory - Only in Win 7 and not XP

[thesuperninja]

Hi All,

Problem Summary : My code runs successfully in Windows XP machine, but the same code gives me Compile Error : Out of memory while running on a Windows 7 machine

Purpose of the code : It is sort of a report generation code where the user can export customized data to a new file. Within the code the following are the basic steps :

1. User gives a custom name for the file which would be exported
2. Data in all sheets is pasted as values
3. Unnecessary sheets get deleted
4. File gets saved as New File

Issue Origin : The problem seems to be happening at step number three above. As soon as my code enters the procedure to execute step no. 3 above I get the error.

Steps Taken by Me : Screen updating has been disabled, calculations have been made manual, alerts have been disabled, after step 2 i even did cutcopymode=false

Code : Below is the code which is not getting executed :

Sub Delete_ResultSheets()

    Dim wSht As Worksheet
    
    For Each wSht In Worksheets
        wSht.Visible = True
    Next
    
    If Worksheets("Model_bkgd").Range("BI46").Value <> 2 Then     ' Raw Data - Query Results
        ActiveWorkbook.Worksheets("Raw Data_Active").Delete
        ActiveWorkbook.Worksheets("Raw Data_Inactive").Delete
    End If
    
    If Worksheets("WFP Assumptions").Range("D104") = "No" Then        ' Only Gap Analysis
        ActiveWorkbook.Worksheets("Output_Gap Analysis").Delete
    End If
    
    If Worksheets("Model_bkgd").Range("BI46").Value <> 3 Then     ' Summary Graphs - Output Charts
        ActiveWorkbook.Worksheets("Output_Charts").Delete
        ActiveWorkbook.Worksheets("Output_RetirementPipelineCharts").Delete
    End If
    
    If Worksheets("Model_bkgd").Range("BI29").Value = 3 Or Worksheets("Model_bkgd").Range("BI46").Value <> 3 Or Worksheets("WFP Assumptions").Range("D104") = "No" Then
        ActiveWorkbook.Worksheets("Output_Gap Analysis Charts").Delete
    End If
    
    If Worksheets("Model_bkgd").Range("BI29").Value = 3 Or Worksheets("Model_bkgd").Range("BI46").Value <> 3 Or Worksheets("WFP Assumptions").Range("D103") = "No" Then
        ActiveWorkbook.Worksheets("Output_Demand Charts").Delete
    End If
    
    If Worksheets("Model_bkgd").Range("BI29").Value = 3 Or Worksheets("Model_bkgd").Range("BI46").Value <> 3 Or Worksheets("WFP Assumptions").Range("D102") = "No" Then
        ActiveWorkbook.Worksheets("Output_Supply Forecast Charts").Delete
    End If
    
    If Worksheets("WFP Assumptions").Range("D102") = "No" Then        ' Only Supply Forecast Tables & Assumptions
        ActiveWorkbook.Worksheets("Output_Supply Forecast").Delete
        ActiveWorkbook.Worksheets("Supply Forecast Assumptions").Delete
    End If
    
    If Worksheets("WFP Assumptions").Range("D101") = "No" Then       ' Only Supply Assessment Tables
        ActiveWorkbook.Worksheets("Output_Demographics").Delete
    End If
    
    If Worksheets("WFP Assumptions").Range("D103") = "No" Then        ' Only Supply Forecast Tables
        ActiveWorkbook.Worksheets("Demand Assumptions").Delete
        ActiveWorkbook.Worksheets("Output_Demand").Delete
    End If
        
    ActiveWorkbook.Worksheets("Model").Delete
    ActiveWorkbook.Worksheets("Supply Forecast").Delete
    ActiveWorkbook.Worksheets("Supply Forecast AHR Data").Delete
    ActiveWorkbook.Worksheets("Supply Forecast Formulas").Delete
    ActiveWorkbook.Worksheets("Demand").Delete
    ActiveWorkbook.Worksheets("Demand_BKGD").Delete
    ActiveWorkbook.Worksheets("Demand Ratio Data").Delete
    ActiveWorkbook.Worksheets("Demand Import Data").Delete
    ActiveWorkbook.Worksheets("Query Results_Active").Delete
    ActiveWorkbook.Worksheets("Query Results_Inactive").Delete
    ActiveWorkbook.Worksheets("Event_Rates").Delete
    ActiveWorkbook.Worksheets("Demographics_Formulas_Function").Delete
    ActiveWorkbook.Worksheets("Demographics_Formulas_JobTitle").Delete
    ActiveWorkbook.Worksheets("PivotData").Delete
    ActiveWorkbook.Worksheets("UserControlPanel").Delete
    ActiveWorkbook.Worksheets("HelpSheet").Delete
    ActiveWorkbook.Worksheets("Colors_Fonts_Borders").Delete
    ActiveWorkbook.Worksheets("Scenario Information").Delete
    ActiveWorkbook.Worksheets("ScenarioGeneration").Delete
    ActiveWorkbook.Worksheets("ScenarioBackground").Delete
    
    ActiveWorkbook.Worksheets("Model_bkgd").Visible = xlSheetVeryHidden
    ActiveWorkbook.Worksheets("Supply Forecast Input_BKGD").Visible = xlSheetVeryHidden
    ActiveWorkbook.Worksheets("Demographics_Formulas_Overall").Visible = xlSheetVeryHidden
    ActiveWorkbook.Worksheets("WFP Assumptions").Activate
    Worksheets("WFP Assumptions").Range("A2").Select
    
End Sub

 

[xenou]

Try putting some saves in every so often. Or, instead of deleting sheets, copy the sheets you want to keep to a new workbook, then close the file with the unwanted sheets (my preference).