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

thesuperninja

New Member
Joined
Dec 29, 2010
Messages
1
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 :
Code:
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,547
Office Version
2013
Platform
Windows
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).
 

Forum statistics

Threads
1,081,420
Messages
5,358,574
Members
400,504
Latest member
RedSquirrel

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top