Excel VBA with Advanced Filter Error messages

[SimoneFick]

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/62540181/excel-vba-with-advanced-filter-error-excel-ran-out-of-resources
https://www.excelforum.com/excel-programming-vba-macros/1320504-excel-vba-with-advanced-filter-error-excel-ran-out-of-resources.html

[snb]

Why don't you post a sample workbook ?

What is the value of variable Vregion ?

Why are columns B to D empty in Row 7 ?

In a criteria range the column'names' should match those of the to be filtered range.
The criteria range will be automatically given a name after mentioning it in the advancedfilter mathod.
No need to name it yourself.

Do not use spaces in a column'name'.

In principle this code suffices:


Sub M_snb()
With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
.SetText Join(Array(Join(Array("MS", "SOH", "Order", "RP", "Format", "Region"), vbTab), Join(Array("4", 0, 0, "Roster", "Corporate", VRegion), vbTab), Join(Array("", "", "", "", "Hyper", ""), vbTab)), vbCr)
.PutInClipboard
End With

with Sheet1
.Paste .Cells(100, 1)
.ListObjects(1).Range.AdvancedFilter 2, .Cells(100, 1).CurrentRegion, sheet2.Cells(1)
.Cells(100, 1).CurrentRegion.ClearContents
end with
End Sub

[SimoneFick]

Please see attached sample workbooks for raw data and template, with sensitive information removed. I made a small sample size.

Vregion is specified earlier in the code from a combox, for this instance "Western Cape"

I use the first row (In this case row 7) just to put a heading above the filter criteria, this is mostly for myself, because as mentioned in my post, there is about 20 different similar cases, so 20 different filter criteria cases. Basically I have a raw data file and the different data needs to be copied with certain criteria and headings to different reports in another workbook.

I've tried using the advanced filter manually and still gives the same errors but also still copies that data correctly...
To try and narrow down the problems I did a couple of tests. When I moved all the headings that it needs to copy to, to the same worksheet and workbook as where the raw data is, there were no error messages. If this means anything?

[snb]

I see no problem.
Activate the 'Result' sheet; run the macro in F8-mode.

[SimoneFick]

Thanks snb, I'll change one of my code sections to match yours and see if it works if I add my variables.

[p45cal]

Formula in cell R9 of the Corporate Order Compliance sheet; remove it. Try again.
The table seems not to adjust its size according to the results of the Advanced Filter.
You could get the VBA to remove the formula, adjust the table size to fit and reinstate the formula, or to calculate the value and put it there as a plain value. Whichever, you need to realise that the table doesn't re-adjust its size automatically.

[khalidasaada]

Hello,

Sorry if posting my question here , but actually I m new and i did not know where i can post it.

i have 2 sheets one is for the data called employee cost which is having a calculation of employees EOS and the other sheet is a report sheet quarterly basis retrieve the date from the first sheet based on formula below as below

Sample of quarterly report

DATE EOS LEAVE TOTAL
26-Aug-20 1,797,331.23 653,240.56 2,450,571.79
30-Jun-20 - - -
30-Sep-20 - - -
31-Dec-20 - - -

the formula in column EOS is =IF(TODAY()=C4(date first cell),'EMPLOYEE COST 2020'!$AF$128,0)

as you notice the criteria based on date means when the condition is true the figures will shows up, but next day it will be gone because the date changed( Today()), therefore I need to keep the data showed in that specific date and not change by date changes.

thanks in advance

kal

[Paul_Hossler]

@khalidasaada --

Welcome to the forum. Please take a minute to read the FAQs in the link in my signature

It's better to start your own thread -- use the [+Post New Thread] top left side


27035

[khalidasaada]

thank you for your reply but actually i cant find this option 27039

[Paul_Hossler]

Click 'Excel' and enter the Excel forum first, and the button should be there

[Sofiya2k]

I have solved my issue by correcting the Wrong Formulas. here (https://thegeekpage.com/excel-ran-out-of-resources-while-attempting-to-calculate-one-or-more-formulas/)

[Aussiebear]

I have solved my issue by correcting the Wrong Formulas. here (https://thegeekpage.com/excel-ran-out-of-resources-while-attempting-to-calculate-one-or-more-formulas/)

Welcome to the VBAX forum Sofiya2k. What a strange response for your very first post?