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

Y

YounesB3

Board Regular
Joined
Mar 28, 2012
Messages
148
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:
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Hi, I used to get this quite regularly and in my experience its almost always a hardware 'challenge'. I've had a quick flick over your code and can't see anything glaring that could be bleeding your memory. 16 open files is a lot though, but to share how I have overcome this previously:

Free:

1.) close all other applications this should free up resource

2.) open task manager and end any non critical process e.g. Google crash handler, itunes manager, Dropbox etc

3.) perhaps rework your code to only have a few files open at one time


Gonna cost money (disclaimer: can't guarantee that they'll work either!):

4.) Install more RAM: how much RAM do you have installed, most people will have the 32 bit version of excel installed that can in theory use a maximum of 4GB. (although I've only really ever seen it use 2GB before I get the cannot complete this task message)

5.) Upgrade to a 64 bit system: what type of system are you running 32bit or 64 bit? a 32bit system can only utilise a max of 4GB RAM, so system processes maybe withholding RAM that excel needs to run. A 64 bit system allows for a lot more RAM to be utilizsed but as mentioned above there is a limit to how much a 32 bit version of excel can utilise.

6.) Upgrade to a 64bit version of office: as mentioned above the 32 bit version of Excel can only utilse so much RAM, where as the 64 bit can use a lot more. You will need to be running a 64 bit system though and there are compatibility issues between files created in 32bit and 64bit version of excel.


Hope that helps
 
Upvote 0
potter_ricky said:
Hi, I used to get this quite regularly and in my experience its almost always a hardware 'challenge'. I've had a quick flick over your code and can't see anything glaring that could be bleeding your memory. 16 open files is a lot though, but to share how I have overcome this previously:

Free:

1.) close all other applications this should free up resource

2.) open task manager and end any non critical process e.g. Google crash handler, itunes manager, Dropbox etc

3.) perhaps rework your code to only have a few files open at one time


Gonna cost money (disclaimer: can't guarantee that they'll work either!):

4.) Install more RAM: how much RAM do you have installed, most people will have the 32 bit version of excel installed that can in theory use a maximum of 4GB. (although I've only really ever seen it use 2GB before I get the cannot complete this task message)

5.) Upgrade to a 64 bit system: what type of system are you running 32bit or 64 bit? a 32bit system can only utilise a max of 4GB RAM, so system processes maybe withholding RAM that excel needs to run. A 64 bit system allows for a lot more RAM to be utilizsed but as mentioned above there is a limit to how much a 32 bit version of excel can utilise.

6.) Upgrade to a 64bit version of office: as mentioned above the 32 bit version of Excel can only utilse so much RAM, where as the 64 bit can use a lot more. You will need to be running a 64 bit system though and there are compatibility issues between files created in 32bit and 64bit version of excel.


Hope that helps
Click to expand...

I can't really do 4 to 6 as it's a company laptop. I'll try to split the macro in 2 or 3 tomorrow see if it works. Thxs!
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
VBA to select partial filename and go back and forth between two files to copy paste
Replies
1
Views
168
shknbk2
S
P
  • Solved
Counter in For Each loop
Replies
2
Views
200
Possum17
P
A
  • Question
Macro to update list to last row on another sheet copy source from multiple sheets
Replies
0
Views
312
Azamamir
A
S
  • Solved
VBA does not continue to copy after blank cell
Replies
8
Views
527
jolivanes
jolivanes
P
  • Solved
Need help with macro Dynamic Range??
Replies
6
Views
176
pennbowl
P

Forum statistics

Threads
1,215,076
Messages
6,122,983
Members
449,092
Latest member
Mr Hughes

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top