将筛选的数据复制到用户通过ComboBox (用户表单或ActiveX)输入筛选条件的另一个工作表
最终,我希望将选择的数据从一个工作表上的主数据集中复制到另一个工作表。为了隔离要复制的相关数据,我计划使用过滤器。我想在工作表中包含一个下拉列表对象,允许用户选择筛选条件
这本质上只是复制Excel数据筛选函数,而不是在位置上限制到要筛选的列。
我假设用户表单或ActiveX ComboBox是实现这一点的方法?这就是我失败的地方。我不知道如何调用用户从ComboBox中选择的值来通知AutoFilter过程。
Sub M_00()
Dim wB As Workbook
Dim wS1, wS2 As Worksheet
'Dim x As ???
Dim rng3 As Range
Dim lrowS3 As Long
Set wB = This.Workbook
Set wS1 = wB.Sheets(1)
Set wS3 = wB.Sheets(3)
'define range for Combo Box drop down list from data on Sheet 3
lrowS3 = wS3.Cells(Rows.Count, 1).End(xlUp).Row
Set rng3 = wS3.Range(Cells(2, 2), Cells(lrowS3, 2))
'Don't know how to
' a) assign my rng3 values to the ComboBox drop down list
' b) call the selection from the ComboBox to inform my AutoFilter
wS1.ComboBox1.List = rng3.Value
x = wS1.ComboBox1.Selection
' turn off any autofilters that may already be active in Sheet 1
wS1.AutoFilterMode = False
' filter column 4 based on combo box selection
wS1.UsedRange.AutoFilter Field:=4, Criteria1:=x
'Once filtered I plan you assign the visible data to an array and copy
'it to the required sheet.
End Sub
https://stackoverflow.com/questions/56169348
复制相似问题