We help IT Professionals succeed at work.

Auto Filter with VBA based on a Range of Cells

112 Views
Last Modified: 2019-01-29
Hello,

Im currently looking for a way to accomplish my task with VBA. I would want to use auto filter based on a range of cells. I have my file attached for use. There are two tabs (data and lookup_value). The data tab has the raw data and the lookup_value has the range of cell I want to be filtered from the raw data.

Example: DATA tab

Label1      Label2      Label3       Label4
1      James      North        $3,400.00
2      Peter      South       $32,400.00
3      Andrew      West       $23,000.00
4      Sam      South       $12,000.00
5      Job              West       $2,300.00
6      Josiah      South       $12,000.00
7      Law        West       $23,000.00
8      Little      South       $45,000.00
9      William      West       $56,900.00
10      David      South       $432,000.00
11      Farai      West       $3,300.00
12      Gary      South       $334,400.00
13      Henry      West       $21,590.00
14      Kevin      South       $231,100.00
15      Katt              West       $23,120.00
16      Leon      South       $32,459.00
17      Lennon      West       $98,554.00
18      Eric              North        $349,321.00
19      Elliot      North        $34,332.00


In the lookup_value, i have a pivot table with LABEL1 in the row label. Some of the values are selected and I want the VBA to lookup the values selected from the pivot table and reflect that on the DATA tab. I also want a filter for LABEL2 to the ones equal to "North".

Your assistance is greatly appreciated.

Thanks,
Lennon G
data_report.xlsx
Comment
Watch Question

Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Lennon GaryJunior Oracle Database Administrator

Author

Commented:
this worked perfectly. just one more additional request.
I would want to copy and paste a cell value to the last row.

in the LOOKUP_VALUE tab I would want to copy A1 (cell value) and paste it to C2 to the last row, in the DATA tab.

AFTER all is done, how would I unfilter?
Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION