I have code in VBA that uses an Advanced filter to fill multiple tables. It worked without any errors before. I upgraded from Office 2013 to Office 365 and suddenly I get error messages on the code...
The Advanced filter runs, fills, and filters the tables correctly but I still get an error message that pops up "Excel ran out of resources while attempting to calculate one or more formulas. As a result these formulas cannot be evaluated". If I press okay another error shows "Run-time Error 1004: Advanced Filter method of range class failed". The file contains no formulas, 30500 rows, 37 columns.
EDIT (added tried solutions)
I've searched for solutions online and tried a couple but none have worked so far. I've tried the following:
- Closing down all Excel applications and run macro again
- closing down all application and only running Excel
- Reduce the core processors for Excel from 4 to 2
- Turn off the Multi-threaded calculation in Advanced options
- Remove all .COM add-ins
- Reduce the file to only use 100 rows and not the full file, tried 50 as well
- Tried manually using the advanced filter, which doesn't work anymore, I get the same error messages.
- I checked all the On Error Resume Next code. They are all enclosed and end with GoTo 0
- Tried removing filters on the extract datasheet and copy to range sheet
- Tried activating the sheet where I'm copying to first before running the filter
- Tried changing the filter criteria to a set range and not whole columns Set CorpOrdCompCrit = .Range("$A$8:$F$10")
I use advanced filter about 20 times in the code, so it's a bit of a problem. One section of the code looks like this (all 20 sections for the advanced filter are structured the same but with different filter criteria, sometimes just 1 filter):
'Create Filter Criteria ranges
With MainWB.Worksheets.Add
.Name = "FltrCrit"
Dim FltrCrit As Worksheet
Set FltrCrit = MainWB.Worksheets("FltrCrit")
End With
With FltrCrit
Dim CorpOrdCompCrit As Range
Dim myLastColumn As Long
'Create Corporate Order Compliance Filter Criteria Range
.Cells(7, "A") = "Corp Order Comp"
.Cells(8, "A") = "MS"
.Cells(9, "A") = "=4"
.Cells(10, "A") = "=4"
.Cells(8, "B") = "SOH"
.Cells(9, "B") = "=0"
.Cells(10, "B") = "=0"
.Cells(8, "C") = "On Order"
.Cells(9, "C") = "=0"
.Cells(10, "C") = "=0"
.Cells(8, "D") = "RP Type"
.Cells(9, "D") = "Roster"
.Cells(10, "D") = "Roster"
.Cells(8, "E") = "Format"
.Cells(9, "E") = "Corporate"
.Cells(10, "E") = "Hyper"
.Cells(8, "F") = "Region"
.Cells(9, "F") = VRegion
.Cells(10, "F") = VRegion
'get last column, set range name
With .Cells
myLastColumn = .Find(What:="*", After:=.Cells(8), LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Set CorpOrdCompCrit = .Range(.Cells(8, "A:A"), .Cells(10, myLastColumn))
End With
'CORPORATE ORDER COMPLIANCE
Dim tblFiltered As ListObject
Dim copyToRng As Range, SDCRange As Range
Set tblFiltered = wb.Worksheets("Corporate Order Compliance").ListObjects("Table_Corporate_Order_Compliance3")
tblFiltered.AutoFilter.ShowAllData
Set SDCRange = wsSDC.ListObjects("Table_SDCdata").Range
Set copyToRng = tblFiltered.HeaderRowRange
'Use Advanced Filter
SDCRange.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=CorpOrdCompCrit, CopyToRange:=copyToRng, Unique:=False
The bottom part is where the error pop's up, on this line for the advanced filter.
'Use Advanced Filter
SDCRange.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=CorpOrdCompCrit, CopyToRange:=copyToRng, Unique:=False
Does anyone know why this is suddenly happening and how I can fix the problem, please?
I posted it on StackOverflow and on Excel Forum 2 months ago but no solutions that worked yet, unfortunately.
Links:
https://stackoverflow.com/questions/...t-of-resources
https://www.excelforum.com/excel-pro...resources.html