excel跨表筛选的宏命令
时间: 2024-06-10 15:02:19 浏览: 18
Excel跨表筛选可以通过宏命令实现。具体操作步骤如下:
1. 打开宏编辑器,选择新建宏。
2. 给宏起一个名称,选择宏的存储位置,点击确定。
3. 在宏编辑器中输入以下代码:
```
Sub CrossSheetFilter()
Dim vCriteria As Variant
Dim rCriteria As Range
Dim rng As Range
Dim wks As Worksheet
Dim wksCrit As Worksheet
Dim sFormula As String
Dim sCriteria As String
Set wksCrit = Worksheets("Criteria") ' Criteria worksheet name
vCriteria = wksCrit.Range("A1:A2").Value ' Criteria range
Set rng = wksCrit.Range("A5:F100") ' Data range
sFormula = "=AND("
For i = 1 To UBound(vCriteria)
sCriteria = vCriteria(i, 1)
If sCriteria <> "" Then
sFormula = sFormula & "OR(" & rng.Columns(i).Address & "=""" & sCriteria & """,ISBLANK(" & rng.Columns(i).Address & ")),"
End If
Next i
sFormula = Left(sFormula, Len(sFormula) - 1) ' Remove the last comma
sFormula = sFormula & ")"
Set wks = Worksheets.Add ' Create a new worksheet to hold the filtered data
wks.Name = "Filtered Data" ' Name the new worksheet
rng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Nothing, CopyToRange:=wks.Range("A1"), Unique:=False, _
Criteria:=sFormula ' Apply the filter to the data range and copy the results to the new worksheet
End Sub
```
4. 修改代码中的参数,包括“Criteria”工作表的名称、筛选条件的单元格范围、数据范围等。
5. 运行宏命令,即可实现跨表筛选。
相关推荐
![doc](https://img-home.csdnimg.cn/images/20210720083327.png)
![7z](https://img-home.csdnimg.cn/images/20210720083312.png)
![rar](https://img-home.csdnimg.cn/images/20210720083606.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)