Microsoft Excel - Excel cannot complete task with available resources

Asked By Dan on 21-Dec-11 12:36 PM

Excel is locking up and crashes when I run the macro listed below. 

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

I have only listed January and Fedurary, but the code is for all twelve months.  What I am trying to do is autofilter to select certain data, then copy and paste on a different worksheet in the same workbook.  Does anyone have any suggestions on how to fix this?

Win 7/Excel 2010

Dan


Sub DataToChart()

'   ===========================================================================================
Application.CutCopyMode = False

Sheets("January").Select


    On Error Resume Next
    Application.DisplayAlerts = False

    Range("A1").Select
    If Range("A1") = "" Then
    Else
   
    Cells.Select
      Selection.AutoFilter
  
    ActiveSheet.Range("$A$1:$AL$44815").AutoFilter Field:=36, Criteria1:=Array( _
      "CC1", "CC2", "CCC3", _
      "CC4", "CC5", _
      "CC6", "CC7", "CC8", _
      "CC9", "CC10", _
      "CC11", "CC12", "CC13" _
      , "CC14", "CC14", "CC15", _
      "CC16", "CC17"), Operator:= _
      xlFilterValues
     
    Columns("A:AL").Select
    Selection.Copy
   
      Sheets("Jan Chart").Select
      Range("AA1").Select
      ActiveSheet.Paste
  
    Range("A1").Select
   
      Sheets("January").Select
      Selection.AutoFilter

    Range("A1").Select
   
   
End If

Application.CutCopyMode = False

Sheets("Feburary").Select


    On Error Resume Next
    Application.DisplayAlerts = False

    Range("A1").Select
    If Range("A1") = "" Then
    Else
   
    Cells.Select
      Selection.AutoFilter
  
    ActiveSheet.Range("$A$1:$AL$44815").AutoFilter Field:=36, Criteria1:=Array( _
      "CC1", "CC2", "CCC3", _
      "CC4", "CC5", _
      "CC6", "CC7", "CC8", _
      "CC9", "CC10", _
      "CC11", "CC12", "CC13" _
      , "CC14", "CC14", "CC15", _
      "CC16", "CC17"), Operator:= _
      xlFilterValues
     
    Columns("A:AL").Select
    Selection.Copy
   
      Sheets("Jan Chart").Select
      Range("AA1").Select
      ActiveSheet.Paste
  
    Range("A1").Select
   
      Sheets("January").Select
      Selection.AutoFilter

    Range("A1").Select
   
   
End If

John D replied to Dan on 21-Dec-11 12:49 PM
Hi
Did you try running one month at a time, just to see if the problem is there.
What type of  computer are you running, e.g. memory,processor and available space left on yout hard drive.
John
wally eye replied to Dan on 21-Dec-11 01:41 PM
This might work a bit better for you.  I tested it with a 318000 row x 26 column worksheet with no error:

Sub DataToChart(ByVal intMonth As Integer)

'   ===========================================================================================
Dim strMonth        As String
Dim strMo         As String

strMonth = Format(DateValue(intMonth & "/1/10"), "mmmm")
strMo = Format(DateValue(intMonth & "/1/10"), "mmm") & " Chart"

If ThisWorkbook.Worksheets(strMonth).Range("A1") > "" Then
    On Error Resume Next
    Application.DisplayAlerts = False

    ThisWorkbook.Worksheets(strMonth).UsedRange.AutoFilter
    ThisWorkbook.Worksheets(strMonth).UsedRange.AutoFilter Field:=2, Criteria1:="CN=*", Operator:=xlFilterValues
    ThisWorkbook.Worksheets(strMonth).Columns("A:AL.Copy _
    Destination:=ThisWorkbook.Worksheets(strMo).Range("A1")
    ThisWorkbook.Worksheets(strMonth).UsedRange.AutoFilter

End If

Application.CutCopyMode = False

End Sub

Just adjust your criteria back to match your original code.  Rather than having 12 separate sections, I would put in a caller routine like this:

Public Sub AllDataToChart

  dim intMonth    as integer

  for intmonth = 1 to 12
    call datatochart(intmonth)
  next intmonth

end sub

Dan replied to wally eye on 21-Dec-11 02:46 PM
I am getting an error here ThisWorkbook.Worksheets(strMonth).Columns("A:AL.Copy _
  Destination:=ThisWorkbook.Worksheets(strMo).Range("A1")
List or separator required
wally eye replied to Dan on 21-Dec-11 03:47 PM
Sorry, somehow my insert key got turned on:

ThisWorkbook.Worksheets(strMonth).Columns("A:AL").Copy _
Destination:=ThisWorkbook.Worksheets(strMo).Range("A1")
Pichart Y. replied to Dan on 21-Dec-11 09:38 PM
Hi Dan,

I also used to face this problem. This comes from the complex of your criteria in auto filter and also the number of filtered data which you copy...this consume huge amount of resouce consumption. (this also happen when we use normal auto filter in spreadsheet).

What I would like to suggest you is...
My scenario is "Select the item "A" and "B", then input information to column E and F".
This some how take time more than auto filter, but there will be no problem about the "Criteria Complication" and "The huge handle data".
  • Instead of auto filter, we use the select case
    • For Each X In Range("B2:B21")
      Select Case X
      Case Is = "A"
      Range("C" & X.Row) = "Selected"
      Case Is = "B"
      Range("C" & X.Row) = "Selected"
      End Select
      Next
  • Then loop to input the select items, to your location...
    • For Each cell In Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row)
      If cell.Value = "Selected" Then
      tgRow = Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Row
      Range("E" & tgRow).Value = cell.Offset(0, -1).Value
      Range("F" & tgRow).Value = cell.Value
      End If
      Next
  • Here is the attachment for you....---> Vba_SelectThenCopy.zip

Try it with your huge data, and please feedback.

Hope this help.

Pichart Y.

Dan replied to Pichart Y. on 22-Dec-11 06:55 AM
I cant get that to work.  Dont know why.
Pichart Y. replied to Dan on 22-Dec-11 09:16 AM
Hi Dan,

could you please attach your file here...then we can see what happen?

pichart Y