VBA Advanced Filter stopped working

bo_danseuer

New Member
Joined
Oct 23, 2012
Messages
6
Office Version
  1. 365
Platform
  1. MacOS
Hi All,

I have been using the exact same advanced filter in my VBA for at least 20 years (yeah, I'm that old...). I use the same format, same names, same everything. This very simple macro has always worked without fail. However, a few months back, it simply stopped working. No error message. Nothing. It just does not filter any more.

Givens:
  1. one large data table ("data import") with that contains various new data imported daily
  2. one dashboard with various pivot tables and graphes
  3. one slicer to allow user to select the reporting date based on client name, year, and month
  4. one filter table (called Monthly Rpt);
  5. named ranges for all the data = "pivot_data";
  6. named ranges for the advanced filter are: search criteria = "find_what"; copy to range = "put_where"
These are givens I have always used without any problems. If I do a manual search and copy, my list range = "pivot_data"; criteria range = "find_what"; copy to range = "put_where" (cf. screenshot below), the manual filter works just fine with no hiccups. The simple macro that should do the same thing (and that has always done the same thing in the past) now does nothing.

Range("pivot_data").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _​
Range("find_what"), CopyToRange:=Range("put_where"), Unique:=False​

Again, I reiterate, this macro has worked for centuries... well a long time.
I read two things elsewhere stating that the data ranges and filter names needed to be cleared. So I've created the following macro that I CALL from within the find and copy macro above but that helps nothing.

Sub delete_filterNames()​
' Delete names​
Range("put_where").Offset(1, 0).Select​
Range(Selection, Selection.End(xlDown)).Select​
Selection.ClearContents​
On Error Resume Next​
With ActiveWorkbook​
.Names("_filterdatabase").Delete​
.Names("Criteria").Delete​
.Names("Extract").Delete​
End With​
On Error GoTo 0​
End Sub​

Any wizards out there who can help?
NB: I am working on sending over an abridged version of the table but it's pretty complex. Please bear with me.
 

Attachments

  • Screenshot 2021-02-23 at 14.46.21.png
    Screenshot 2021-02-23 at 14.46.21.png
    129.5 KB · Views: 6

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Watch MrExcel Video

Forum statistics

Threads
1,127,756
Messages
5,626,675
Members
416,200
Latest member
Pulsar3000

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top