Filter data using 2 Combobox and simultaneously filter data in another sheet using VBA code.

babaso_tawase

Board Regular
Joined
Feb 5, 2017
Messages
73
Office Version
  1. 2007
Platform
  1. Windows
Hi Sir/Madam,
I have data in two tables in sheet1. I want use combobox filter (at D1 and D4 cell) so data by selecting combobox dropdown number, filter data equal to that number of rows, simultaneously filter data in sheet2.
I used below VBA code for table 1 but gives error at red coloured text line as " Runtime error "1004". Autofilter method of range class failed. Please suggest VBA code code for two table's combobox.

Private Sub ComboBox1_Change()
Sheet1.Unprotect Password:="a"
Sheet2.Unprotect Password:="a"
Dim points As Range
With Worksheets("A COPY")
Set points = .Range("d1")
End With
With Worksheets("A COPY")
With .Range("B7:g17")
.AutoFilter Field:=1, Criteria1:="<=" & points, Operator:=xlFilterValues, Visibledropdown:=False
End With
End With
With Worksheets("A COPY")
Set points = .Range("d1")
End With
With Worksheets("P COPY")
With .Range("b7:g17")
.AutoFilter Field:=1, Criteria1:="<=" & points, Operator:=xlFilterValues, Visibledropdown:=False

End With
End With
Sheet1.Protect Password:="a"
Sheet2.Protect Password:="a"
End Sub

Sheet 1:
RS trail 070624.xlsm
ABCDEFG
110
2Known
3
42
5Unknown
6Known
7Sr.noNoXColumn1A%
811P1210
922D1221202.21.
1033D
1144P
1255D122
1366D
1477P
1588D
1699P
171010D
18
19Unknown
20Sr.noNoXNamea%
2111asdsdsaa
2223asdsdb1.56
2334b
2445c2.56
2556
2667
2778
2889
29955
301010
A COPY



Sheet2:
RS trail 070624.xlsm
ABCDEFG
1
2
3
4
5
6Known
7Sr.noNoXColumn1A%
811P1210
922D1221202.21.
1033D
1144P
1255D122
1366D
1477P
1588D
1699P
171010D
18
19Unknown
20Sr.noNoXNamea%
2111asdsdsaa
2223asdsdb1.56
2334b
2445c2.56
2556
2667
2778
2889
29955
301010
P COPY
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Forum statistics

Threads
1,224,812
Messages
6,181,083
Members
453,021
Latest member
Justyna P

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