VBA to apply Filter accross multiple sheets with the same range

Ramadan

Board Regular
Joined
Jan 20, 2024
Messages
154
Office Version
  1. 2021
Platform
  1. Windows
I'm trying to use Filter function to collect data from the same ranges in multiple sheets based on match with "Parial Text " in cell "$B$3" but it seems that Filter Function doesn't work for multiple sheets while when testing the formula for each sheet individually it works smoothly. this is my formula for 2 sheets only but it gives me only the first sheet data

=FILTER(District1!B:D,(ISNUMBER(SEARCH(B3,District1!G:G)))+(ISNUMBER(SEARCH(B3,District1!I:I)))+(ISNUMBER(SEARCH(B3,District1!K:K)))+(ISNUMBER(SEARCH(B3,District1!M:M))),FILTER('Carnell'!B:D,(ISNUMBER(SEARCH(B3,'Carnell'!G:G)))+(ISNUMBER(SEARCH(B3,'Carnell'!I:I)))+(ISNUMBER(SEARCH(B3,'Carnell'!K:K)))+(ISNUMBER(SEARCH(B3,'Carnell'!M:M)))))

It's to collect data Col "B:D" from each sheet if cel "$B$3" = any cell in columns "G,I,K,M" at any sheet. All sheets has the same table and same format just different data and I need to repeat this function 7 times for 7 sheets
I've been searching long time to solve such issue but only found that it can be done with VSTACK function which is not available in my office version 2021

Is there any suggestions to do this through VBA ??
 

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
Back
Top