Excel VBA with Advanced Filter “Error: Excel Ran out of resources”

I have posted this question on Stackoverflow as well, it's been a week and unfortunately no solutions yet...
https://stackoverflow.com/questions/...03012_62540181

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". this is strange since it still does the filtering and filling correctly... The file contains no formulas, 30500 rows, 37 columns.

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 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):

Please Login or Register  to view this content.

The bottom part is where the error pop's up, on this line for the advanced filter.

Please Login or Register  to view this content.

Does anyone know why this is suddenly happening and how I can fix the problem, please?

Hi Sintek,

Just so I'm clear. Do you want me to upload a small sample size of ONLY the final table with data was filtered and filled in? The numbers are correct, I did check that just in case...
Not sure how this would help but if it sheds some light on my situation I will gladly do that.

The reason I'm using Advanced filter about 20 times is because I need to fill different reports with different data, from either the same raw data table or different multiple ones. I'm not sure if there is an easier way, but it there is I'm all ears!

I've attached a sample file with the template/table that it copies to and the data that it added (which is the correct amount).
I've added another sheet with the Filter criteria on there as well, and added 2 more examples as it would normally look. Normally the filter criteria is in the same workbook as the raw data that gets filtered(just a different sheet). This has worked absolutely fine before.

VRegion is a string variable that the user sets right at the start with a listbox. In this cases it's "EC".

Yes, all the advanced filter setups run the same/have been coded the same, if that's what you're asking?

Just to show where the advanced filter runs after each other in this example, If added another part of the codes below. No I don;t think I clear the ranges, I sometimes use the same raw data range to filter, so no need to clear, and the Filter criteria is all set for there own range and variable (see below). Will it be best to change this?

Please Login or Register  to view this content.

Please let me know if I need to supply more data or code that will help.
I appreciate the help!

I've attached a new workbook with the 'Franchise' templeate on as well, adn added another workbook with sample SDC data. I've moved the 'FltrCrit' sheet to the raw data (SDC workbook) as well as it would normally be. I know the code I pasted creates the FltrCrit sheet, but I added it just for exmaple, it can obviously be deleted if neccessary.

I've manually tested the advanced filter on the test samples I've attached. Strange thing is that is doesn't work now manually...before it did. I have to convert the template table to range, do the advanced filter, then I still get the same error, but it still filters and copies correctly (as with the VBA code).

To explain what the code does:
I have a big raw data file and I need to create different reports from it, thus needing to filter different criteria and copy that data to the specific report/template. In this case Franchise report and Corporate report. But there are more reports with different criteria from different files as well.
The problem I'm having is that it keeps giving me an error (as explained in the main post), even though it is correctly filtering and copying the correct data.

Yes, it's the same throughout for all Advanced Filter criteria. When I started coding it I was looking for a way not to hard code it but I couldn't find any suggestions, unfortunately. I'll be very happy if I don't have to do it that way.

Thank you! I really appreciate the effort!