Excel cannot complete this task with available resources. Choose less data or close

YounesB3 · #1 11-14-2012, 03:29 PM

Hello everyone, I'm having an issue when I try to run a macro (16 different workbooks open):

"Excel cannot complete this task with available resources. Choose less data or closer other applications"

Here's the macro (I replaced the titles of the files for policies issues):

Code:

Sub ExportInfo()
'
' ExportInfo Macro
'
'
    Windows("ABCD.xlsm").Activate
    Sheets("QryPartPlanFTV").Select
    Range("A1:AF1").Select
    Selection.AutoFilter
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$AF$99999").AutoFilter Field:=16, Criteria1:= _
        "Visibilité 30 jrs"
        
    
    Windows("A.xlsx").Activate
    Sheets("New Data").Select
    Columns("A:AF").Select
    Selection.Copy
    Sheets("Previous Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Windows("ABCD.xlsm").Activate
    ActiveSheet.Range("$A$1:$AF$99999").AutoFilter Field:=17, Criteria1:="2.01"
    Columns("A:AF").Select
    Application.CutCopyMode = False
    Selection.Copy
    
    Windows("A.xlsx").Activate
    Sheets("New Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    
    Windows("B.xlsx").Activate
    Sheets("New Data").Select
    Columns("A:AF").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Previous Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
Windows("ABCD.xlsm").Activate
    ActiveSheet.Range("$A$1:$AF$99999").AutoFilter Field:=17, Criteria1:=Array( _
        "2.02", "2.03", "2.06", "2.09"), Operator:=xlFilterValues
    Columns("A:AF").Select
    Application.CutCopyMode = False
    Selection.Copy
    
Windows("B.xlsx").Activate
    Sheets("New Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    
    Windows("C.xlsx").Activate
    Sheets("New Data").Select
    Columns("A:AF").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Previous Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   
 Windows("ABCD.xlsm").Activate
    ActiveSheet.Range("$A$1:$AF$99999").AutoFilter Field:=17, Criteria1:=Array( _
        "2.14", "5.01", "5.11", "5.13", "5.17"), Operator:=xlFilterValues
    Columns("A:AF").Select
    Application.CutCopyMode = False
    Selection.Copy
    
    Windows("C.xlsx").Activate
    Sheets("New Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Windows("D.xlsx").Activate
    Sheets("New Data").Select
    Columns("A:AF").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Previous Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Windows("ABCD.xlsm").Activate
    ActiveSheet.Range("$A$1:$AF$99999").AutoFilter Field:=17, Criteria1:=Array( _
        "2.12", "3.03", "3.04"), Operator:=xlFilterValues
    Columns("A:AF").Select
    Application.CutCopyMode = False
    Selection.Copy
    
    Windows("D.xlsx").Activate
    Sheets("New Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Windows("E.xlsx").Activate
    Sheets("New Data").Select
    Columns("A:AF").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Previous Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Windows("ABCD.xlsm").Activate
    ActiveSheet.Range("$A$1:$AF$99999").AutoFilter Field:=17, Criteria1:= _
        "=3.01", Operator:=xlOr, Criteria2:="=3.06_08"
    Columns("A:AF").Select
    Application.CutCopyMode = False
    Selection.Copy
    
    Windows("E.xlsx").Activate
    Sheets("New Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Windows("F.xlsx").Activate
    Sheets("New Data").Select
    Range("A1").Select
    Columns("A:AF").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Previous Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Windows("ABCD.xlsm").Activate
    ActiveSheet.Range("$A$1:$AF$99999").AutoFilter Field:=17, Criteria1:="5.18"
    Columns("A:AF").Select
    Application.CutCopyMode = False
    Selection.Copy
    
    Windows("F.xlsx").Activate
    Sheets("New Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Windows("G.xlsx").Activate
    Sheets("New Data").Select
    Columns("A:AF").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Previous Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Windows("ABCD.xlsm").Activate
    ActiveSheet.Range("$A$1:$AF$99999").AutoFilter Field:=17, Criteria1:=Array( _
        "2.07", "4.12", "5.12", "5.15", "5.16"), Operator:=xlFilterValues
    Columns("A:AF").Select
    Application.CutCopyMode = False
    Selection.Copy
    
    Windows("G.xlsx").Activate
    Sheets("New Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Windows("H.xlsx").Activate
    Columns("A:AF").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Previous Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Windows("ABCD.xlsm").Activate
    ActiveSheet.Range("$A$1:$AF$99999").AutoFilter Field:=17, Criteria1:=Array( _
        "2.13", "2.08", "3.07", "5.19", "5.07", "5.08"), Operator:=xlFilterValues
    Columns("A:AF").Select
    Application.CutCopyMode = False
    Selection.Copy
   
 Windows("H.xlsx").Activate
    Sheets("New Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    
    Windows("I.xlsx").Activate
    Sheets("New Data").Select
    Columns("A:AF").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Previous Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Windows("ABCD.xlsm").Activate
    ActiveSheet.Range("$A$1:$AF$99999").AutoFilter Field:=17, Criteria1:=Array( _
        "4.01", "4.02", "4.03", "6.23", "6.24"), Operator:=xlFilterValues
    Columns("A:AF").Select
    Application.CutCopyMode = False
    Selection.Copy
    
    Windows("I.xlsx").Activate
    Sheets("New Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    
    Windows("J.xlsx").Activate
    Sheets("New Data").Select
    Columns("A:AF").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Previous Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Windows("ABCD.xlsm").Activate
    ActiveSheet.Range("$A$1:$AF$99999").AutoFilter Field:=17, Criteria1:="6.11_12"
    Columns("A:AF").Select
    Application.CutCopyMode = False
    Selection.Copy
    
    Windows("J.xlsx").Activate
    Sheets("New Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    
    Windows("K.xlsx").Activate
    Sheets("New Data").Select
    Columns("A:AF").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Previous Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
Windows("ABCD.xlsm").Activate
    ActiveSheet.Range("$A$1:$AF$99999").AutoFilter Field:=17, Criteria1:= _
        "=6.13", Operator:=xlOr, Criteria2:="=6.22"
    Columns("A:AF").Select
    Application.CutCopyMode = False
    Selection.Copy
    
    Windows("K.xlsx").Activate
    Sheets("New Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Windows("L.xlsx").Activate
    Sheets("New Data").Select
    Columns("A:AF").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Previous Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Windows("ABCD.xlsm").Activate
    ActiveSheet.Range("$A$1:$AF$99999").AutoFilter Field:=17, Criteria1:= _
        "=3.02", Operator:=xlOr, Criteria2:="=5.05"
    Columns("A:AF").Select
    Application.CutCopyMode = False
    Selection.Copy
   
 Windows("L.xlsx").Activate
    Sheets("New Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Windows("M.xlsx").Activate
    Sheets("New Data").Select
    Columns("A:AF").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Previous Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Windows("ABCD.xlsm").Activate
    ActiveSheet.Range("$A$1:$AF$99999").AutoFilter Field:=17, Criteria1:= _
        "=5.03", Operator:=xlOr, Criteria2:="=6.29"
    Columns("A:AF").Select
    Application.CutCopyMode = False
    Selection.Copy
    
Windows("M.xlsx").Activate
    Sheets("New Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Windows("N.xlsx").Activate
    Sheets("New Data").Select
    Columns("A:AF").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Previous Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Windows("ABCD.xlsm").Activate
    ActiveSheet.Range("$A$1:$AF$99999").AutoFilter Field:=17, Criteria1:= _
        "=2.11", Operator:=xlOr, Criteria2:="=2.05"
    Columns("A:AF").Select
    Application.CutCopyMode = False
    Selection.Copy
    
Windows("N.xlsx").Activate
    Sheets("New Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

What Gives? Seems like a memory problem, but I need these 16 files separated...

N.B.: Cross posted:

http://www.mrexcel.com/forum/excel-q...ml#post3317544

http://www.excelforum.com/excel-prog...ose-other.html

YounesB3 · #2 11-16-2012, 02:40 PM

I solved the issue by adding the following at the beginning of each file:

Code:

    Workbooks.Open Filename:= _
        "?:\(path to the file)", UpdateLinks:=0

And this at the end:

Code:

    ActiveWorkbook.Save
    ActiveWindow.Close

Kind Regards,
YB3

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Task Indicator - All predecessors complete true/false, y/n	jdove78	Project	2	10-10-2012 05:53 PM
excel cannot complete this task with available resources - vlookup	Taisho	Excel	4	01-27-2012 03:09 AM
How to set task to be done by 4 resources each one after another?	lukusm	Project	1	01-16-2012 07:21 AM
Close outlook via task manager	scfisher	Outlook	0	12-28-2011 07:28 AM
Stop the % Complete of subatasks from counting against the Main task	bzeyger	Project	0	06-08-2010 06:19 AM