|
透视表页字段多级联动
页字段多级联动.rar
(10.56 KB, 下载次数: 800)
- Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
- On Error Resume Next
- Dim i%, j%, n%, item$, rng As Range, pvi As PivotItem
- Application.ScreenUpdating = False
- Application.EnableEvents = False
-
- Set pvt = Sh.PivotTables(1)
- Set rng = ThisWorkbook.Sheets("数据源").UsedRange
- n = rng.Rows.Count
-
- For Each pvi In pvt.PivotFields("城市").PivotItems
- pvi.Visible = True
- Next
-
- For j = 2 To n
- If rng(j, 1) <> [b2] And [b2] <> "(全部)" Then
- item = rng(j, 2)
- pvt.PivotFields("城市").PivotItems(item).Visible = False
- End If
- Next
-
- Sh.PivotTables(1).PivotCache.Refresh
- Application.EnableEvents = True
- Application.ScreenUpdating = True
- End Sub
复制代码 修改一下代码,提高运行效率- Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
- On Error Resume Next
- Dim i%, j%, n%, item0$, item1$, item2$, rng As Range, pvi As PivotItem
- Application.ScreenUpdating = False
- Application.EnableEvents = False
- Set pvt = Sh.PivotTables(1)
- Set rng = ThisWorkbook.Sheets("数据源").UsedRange
- n = rng.Rows.Count
- item0 = pvt.PivotFields("区域").CurrentPage
- For Each pvi In pvt.PivotFields("城市").PivotItems
- pvi.Visible = True
- Next
- For Each pvi In pvt.PivotFields("城市").PivotItems
- item2 = pvi
- For j = 2 To n
- If rng(j, 2) = item2 Then
- item1 = rng(j, 1)
- If item1 <> item0 And item0 <> "(All)" Then
- pvi.Visible = False
- End If
- Exit For
- End If
- Next
- Next
- Sh.PivotTables(1).PivotCache.Refresh
- Application.EnableEvents = True
- Application.ScreenUpdating = True
-
- End Sub
-
页字段多级联动.rar
(262.09 KB, 下载次数: 2136)
复制代码
[ 本帖最后由 jssy 于 2010-4-20 14:37 编辑 ] |
评分
-
1
查看全部评分
-
|