"Excel Cannot complete task with available resources"

Question

I have no clue what is causing this issue. I have a simple macro that creates new tabs and names them, then filters data from one tab and pastes it to their corresponding tab.

Here is my code:

Sub Tabs()

Dim wbRecovery As Workbook
Dim wsFac As Worksheet

Set wbRecovery = ThisWorkbook
Set wsFac = wbRecovery.Sheets("Facility Rec Bucket & Year")

With wbRecovery
Sheets.Add After:=Sheets(Sheets.Count), Count:=17
Sheets(5).Name = "DANES"
Sheets(6).Name = "DCEND"
Sheets(7).Name = "DCHED"
Sheets(8).Name = "DCNUR"
Sheets(9).Name = "DCRIC"
Sheets(10).Name = "DEMER"
Sheets(11).Name = "DHEMA"
Sheets(12).Name = "DMED"
Sheets(13).Name = "DNEUR"
Sheets(14).Name = "DNSUR"
Sheets(15).Name = "DOBGY"
Sheets(16).Name = "DOPHT"
Sheets(17).Name = "DPEDS"
Sheets(18).Name = "DPMR"
Sheets(19).Name = "DPSYC"
Sheets(20).Name = "DRADS"
Sheets(21).Name = "DSURG"
End With

wsFac.Range("A1").AutoFilter Field:=1, Criteria1:=("DANES"), _
        Operator:=xlFilterValues
wsFac.Cells.Copy Destination:=wbRecovery.Sheets("DANES").Range("A1")
wsFac.Range("A1").AutoFilter Field:=1, Criteria1:=("DCEND"), _
        Operator:=xlFilterValues
wsFac.Cells.Copy Destination:=wbRecovery.Sheets("DCEND").Range("A1")
wsFac.Range("A1").AutoFilter Field:=1, Criteria1:=("DCHED"), _
        Operator:=xlFilterValues
wsFac.Cells.Copy Destination:=wbRecovery.Sheets("DCHED").Range("A1")
wsFac.Range("A1").AutoFilter Field:=1, Criteria1:=("DCNUR"), _
        Operator:=xlFilterValues
wsFac.Cells.Copy Destination:=wbRecovery.Sheets("DCNUR").Range("A1")
wsFac.Range("A1").AutoFilter Field:=1, Criteria1:=("DCRIC"), _
        Operator:=xlFilterValues
wsFac.Cells.Copy Destination:=wbRecovery.Sheets("DCRIC").Range("A1")
wsFac.Range("A1").AutoFilter Field:=1, Criteria1:=("DEMER"), _
        Operator:=xlFilterValues
wsFac.Cells.Copy Destination:=wbRecovery.Sheets("DEMER").Range("A1")
wsFac.Range("A1").AutoFilter Field:=1, Criteria1:=("DHEMA"), _
        Operator:=xlFilterValues
wsFac.Cells.Copy Destination:=wbRecovery.Sheets("DHEMA").Range("A1")
wsFac.Range("A1").AutoFilter Field:=1, Criteria1:=("DMED"), _
        Operator:=xlFilterValues
wsFac.Cells.Copy Destination:=wbRecovery.Sheets("DMED").Range("A1")
wsFac.Range("A1").AutoFilter Field:=1, Criteria1:=("DNEUR"), _
        Operator:=xlFilterValues
wsFac.Cells.Copy Destination:=wbRecovery.Sheets("DNEUR").Range("A1")
wsFac.Range("A1").AutoFilter Field:=1, Criteria1:=("DNSUR"), _
        Operator:=xlFilterValues
wsFac.Cells.Copy Destination:=wbRecovery.Sheets("DNSUR").Range("A1")
wsFac.Range("A1").AutoFilter Field:=1, Criteria1:=("DOBGY"), _
        Operator:=xlFilterValues
wsFac.Cells.Copy Destination:=wbRecovery.Sheets("DOBGY").Range("A1")
wsFac.Range("A1").AutoFilter Field:=1, Criteria1:=("DOPHT"), _
        Operator:=xlFilterValues
wsFac.Cells.Copy Destination:=wbRecovery.Sheets("DOPHT").Range("A1")
wsFac.Range("A1").AutoFilter Field:=1, Criteria1:=("DPEDS"), _
        Operator:=xlFilterValues
wsFac.Cells.Copy Destination:=wbRecovery.Sheets("DPEDS").Range("A1")
wsFac.Range("A1").AutoFilter Field:=1, Criteria1:=("DPMR"), _
        Operator:=xlFilterValues
wsFac.Cells.Copy Destination:=wbRecovery.Sheets("DPMR").Range("A1")
wsFac.Range("A1").AutoFilter Field:=1, Criteria1:=("DPSYC"), _
        Operator:=xlFilterValues
wsFac.Cells.Copy Destination:=wbRecovery.Sheets("DPSYC").Range("A1")
wsFac.Range("A1").AutoFilter Field:=1, Criteria1:=("DRADS"), _
        Operator:=xlFilterValues
wsFac.Cells.Copy Destination:=wbRecovery.Sheets("DRADS").Range("A1")
wsFac.Range("A1").AutoFilter Field:=1, Criteria1:=("DSURG"), _
        Operator:=xlFilterValues
wsFac.Cells.Copy Destination:=wbRecovery.Sheets("DSURG").Range("A1")




End Sub

When I run this code as is, after the OBGY tab is pasted I get the error "Excel Cannot complete task with available resources ". The tab where the data is being copied from is not abnormally large, it's only 566 rows and 14 columns wide (I wouldn't consider that large at all for Excel). If I run the lines of code individually or in chunks of 5-8 it works perfect. All the data is copied and pasted correctly but when I run it as a whole I get the same issue.

Is there something wrong with my computer or Excel itself?

I deleted a lot of stuff off my desktop so I didn't think it was a storage issue. Also when I do the process manually I have no issues. I have also tried Application.CutCopyMode = False and that hasn't worked either

I suspect that is because you are using `wsFac.Cells.Copy` over and over again which is hogging your resources. After the autofilter copy only the visible cells. And when I say visible cells then see [THIS](http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s) — Siddharth Rout, Nov 10 '15 at 16:48
You might want to add `Application.CutCopyMode = False` after each "paste" and a few `DoEvents` if it's not enough. If it's still not enough, you'll need to change your `.Copy`, to something like `Range.Resize.Value=Range.SpecialsCells.Value` (I don't have a link for all of this but at least you'll have the keywords! ;) ) And as suggested by @SiddharthRout : if you use the same table, create a named range and use `wsFac.Range("Name_of_NamedRange").Copy` — R3uK, Nov 10 '15 at 16:50
Wow thanks!! Visible Cells worked like a charm! And it's much faster now. I guess me being lazy really cost me on this one — MCJNY1992, Nov 10 '15 at 16:58
you could also refactor your code to places all the names into an array and loop through the array to add the sheet name, name it, filter the main sheet and copy the visible cells onto the new sheet. would make it easier to update going forward (and to read to :)) — Scott Holtzman, Nov 10 '15 at 17:05

"Excel Cannot complete task with available resources"

0 Answers0