如果我将区域语言格式设置为英语(英国),我对此过滤器有问题,如果我使用英语(美国)则效果很好 . 我的问题是过滤器不会使5月30日的选择不可见,它在该调用中失败并转到错误部分 .
http://wikisend.com/download/138750/Pivottable.xls
这是一个已知的错误? http://support.microsoft.com/default.aspx?scid=kb;en-us;114822&Product=xlw
它过滤日期类似于发布Filter pivottable in Excel 2003
Sub Filter()
Dim PvtItem As PivotItem
Dim ws As Worksheet
On Error GoTo Whoa1
Set ws = Sheets("pivot")
'~~> Show All
For Each PvtItem In ws.PivotTables("PivotTable1").PivotFields("Date").PivotItems
PvtItem.Visible = True
Next
On Error GoTo Whoa2 '<~~ If no match found in Pivot
'~~> Show Only the relevant
For Each PvtItem In ws.PivotTables("PivotTable1").PivotFields("Date").PivotItems
If Format(PvtItem.Value, "DD/MM/YYYY") <> Format(Range("today"), "DD/MM/YYYY") Then
PvtItem.Visible = False
End If
Next
Exit Sub
Whoa1:
MsgBox Err.Description
Exit Sub
Whoa2:
'~~> Show All
For Each PvtItem In ws.PivotTables("PivotTable1").PivotFields("Date").PivotItems
PvtItem.Visible = True
Next
End Sub
1 回答
TRIED AND TESTED IN EXCEL 2003 (With UK Settings)
右键单击快照中显示的日期字段,然后单击
Field Settings
,然后单击Number
. 如图所示设置日期格式 . 现在尝试相同的代码 .