babaso_tawase
Board Regular
- Joined
- Feb 5, 2017
- Messages
- 73
- Office Version
- 2007
- Platform
- 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:
Sheet2:
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 | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | 10 | ||||||||
2 | Known | ||||||||
3 | |||||||||
4 | 2 | ||||||||
5 | Unknown | ||||||||
6 | Known | ||||||||
7 | Sr.no | No | X | Column1 | A | % | |||
8 | 1 | 1 | P | 12 | 10 | ||||
9 | 2 | 2 | D | 1221 | 20 | 2.21. | |||
10 | 3 | 3 | D | ||||||
11 | 4 | 4 | P | ||||||
12 | 5 | 5 | D | 122 | |||||
13 | 6 | 6 | D | ||||||
14 | 7 | 7 | P | ||||||
15 | 8 | 8 | D | ||||||
16 | 9 | 9 | P | ||||||
17 | 10 | 10 | D | ||||||
18 | |||||||||
19 | Unknown | ||||||||
20 | Sr.no | No | X | Name | a | % | |||
21 | 1 | 1 | asdsd | sa | a | ||||
22 | 2 | 3 | asdsd | b | 1.56 | ||||
23 | 3 | 4 | b | ||||||
24 | 4 | 5 | c | 2.56 | |||||
25 | 5 | 6 | |||||||
26 | 6 | 7 | |||||||
27 | 7 | 8 | |||||||
28 | 8 | 9 | |||||||
29 | 9 | 55 | |||||||
30 | 10 | 10 | |||||||
A COPY |
Sheet2:
RS trail 070624.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | |||||||||
2 | |||||||||
3 | |||||||||
4 | |||||||||
5 | |||||||||
6 | Known | ||||||||
7 | Sr.no | No | X | Column1 | A | % | |||
8 | 1 | 1 | P | 12 | 10 | ||||
9 | 2 | 2 | D | 1221 | 20 | 2.21. | |||
10 | 3 | 3 | D | ||||||
11 | 4 | 4 | P | ||||||
12 | 5 | 5 | D | 122 | |||||
13 | 6 | 6 | D | ||||||
14 | 7 | 7 | P | ||||||
15 | 8 | 8 | D | ||||||
16 | 9 | 9 | P | ||||||
17 | 10 | 10 | D | ||||||
18 | |||||||||
19 | Unknown | ||||||||
20 | Sr.no | No | X | Name | a | % | |||
21 | 1 | 1 | asdsd | sa | a | ||||
22 | 2 | 3 | asdsd | b | 1.56 | ||||
23 | 3 | 4 | b | ||||||
24 | 4 | 5 | c | 2.56 | |||||
25 | 5 | 6 | |||||||
26 | 6 | 7 | |||||||
27 | 7 | 8 | |||||||
28 | 8 | 9 | |||||||
29 | 9 | 55 | |||||||
30 | 10 | 10 | |||||||
P COPY |