筛选数据透视表(vba)的最后一项 [英] Filter Last items of Pivot table (vba)

问题描述

快照我有一个数据透视表，只需要显示最后13个项目(星期).下面是我使用的代码.但是，无论该项目在透视图中的可见状态如何，即使将其强制为True/False，它也不会更改.已经尝试过if/else语句，但可见状态仍然不变.

SNapshot I have a pivot table that needs to show only the last 13 items (week). Below is teh code i used. However, what ever the Visible status of the item in the pivot, it does not change even forcing it to True/False. Already tried an if/else statement but still Visible state does not change.

对此有所帮助.谢谢！

Sub ShowLastXDays()
Dim pi As PivotItem
Dim lLoop As Long
Dim pt As PivotTable
Dim pf As PivotField
Dim lCount As Long
Dim lDays As Long
Dim Count As Long

On Error Resume Next
lDays = 13


' Application.ScreenUpdating = False
Set pt = ActiveSheet.PivotTables("WeeklyPivot")
' Count = pt.PivotFields("[FTYieldData].[Week].[Week]").PivotItems.Count

  Set pf = ActiveSheet.PivotTables("WeeklyPivot").PivotFields("[FTYieldData].[Week].[Week]")

        For Each pi In pf.PivotItems
            pi.Visible = False  'issue encountered. after False code, pi.VISIBLE value remains TRUE (not skipped)
        Next pi

    With pt.PivotFields("[FTYieldData].[Week].[Week]")
        Count = .PivotItems.Count
        For lLoop = .PivotItems.Count To 1 Step -1
            .PivotItems(lLoop).Visible = True  'issue encountered. after TRUE code, since earlier it was not change to FALSE (no error encountered)
            lCount = lCount + 1
            If lCount = lDays Then Exit For
        Next lLoop
    End With

  On Error GoTo 0

推荐答案

问题:

您代码中的
• .ClearFilters使所有项目一次可见.因此，隐藏所有项目的循环不再有效.之后，您要循环将其中某些项目设置为Visible，由于所有项目均已可见，因此不会应用.
• On Error Resume Next.您不能隐藏数据透视字段"中的所有项目，这会给您一个错误，至少应显示一个项目.因此，在您的第一个循环中，您将得到一个错误.

• .ClearFilters in your code makes all the Items Visible at once. So your loop that hides all the items is not in effect any more. After that you are looping to set some of those items to Visible, which is not applied as all the items are already visible.
• On Error Resume Next. You cannot hide all the items in a Pivot Field, that should give you an Error, at least one item should be visible. So in your 1st Loop you will get an Error.

尝试:

    Sub ShowLastXDays()
    Dim pf As PivotField
    Dim i As Integer

        ActiveSheet.PivotTables("WeeklyPivot").RefreshTable
        ActiveSheet.PivotTables("WeeklyPivot").PivotCache.MissingItemsLimit = xlMissingItemsNone


        Set pf = ActiveSheet.PivotTables("WeeklyPivot").PivotFields("[FTYieldData].[Week].[Week]")

        For i = 1 To pf.PivotItems.Count
            If i > pf.PivotItems.Count - 13 Then
                  pf.PivotItems(i).Visible = True
            Else: pf.PivotItems(i).Visible = False
            End If
        Next i

    End Sub

工作:

注意:使用On Error Resume Next并不是一种好方法，因为它只会跳过错误，有时在调试时会非常混乱.相反，您应该尝试处理错误.

Note: Using On Error Resume Next is not a good approach as it only skips the error, which is sometimes very confusing while Debugging. Instead you should try to Handle the Errors.

这篇关于筛选数据透视表(vba)的最后一项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！