使用宏过滤问题以在Excel 2010 VBA中创建数据透视表 [英] Filtering Issues with Macro for Creating Pivot Table in Excel 2010 VBA

问题描述

我是VBA的新手，正在尝试编写一个将创建数据透视表的宏.我需要过滤各个字段，并尝试使用PivotFilters.Add和PivotItems仅允许某些事情通过...有时它可以工作，但有时它会引发错误.以下代码可以正常工作:

I am new to VBA and am trying to write a macro that will create a pivot table. I need to filter various fields and have tried using PivotFilters.Add and PivotItems to only let certain things through...sometimes it works, but other times it throws errors. The following code works just fine:

Sub CreatePivot()
Dim objTable As PivotTable, objField As PivotField

ActiveWorkbook.Sheets("CP Monthly Data").Select
Range("A1").Select
Set objTable = Sheet1.PivotTableWizard
objTable.Name = "Resource Requests"
objTable.InGridDropZones = True
objTable.RowAxisLayout xlTabularRow

Set objField = objTable.PivotFields("Company name")
objField.Orientation = xlRowField
objField.Position = 1

Set objField = objTable.PivotFields("Probability Status")
objField.Orientation = xlRowField
objField.Position = 2
objField.PivotItems("X - Lost - 0%").Visible = False
objField.PivotItems("X - On Hold - 0%").Visible = False
objField.AutoSort xlDescending, "Probability Status"

Set objField = objTable.PivotFields("Project")
objField.Orientation = xlRowField
objField.Position = 3

Set objField = objTable.PivotFields("Project manager")
objField.Orientation = xlRowField
objField.Position = 4

Set objField = objTable.PivotFields("Resource name")
objField.Orientation = xlRowField
objField.Position = 5
objField.AutoSort xlAscending, "Resource name"

Set objField = objTable.PivotFields("June, 2012")
objField.Orientation = xlDataField
objField.Function = xlSum
objField.NumberFormat = "##"
objField.Caption = "June"

Set objField = objTable.PivotFields("Workgroup Name")
objField.Orientation = xlPageField
objField.PivotItems("ATG").Visible = False
objField.PivotItems("India - ATG").Visible = False
objField.PivotItems("India - Managed Middleware").Visible = False

Application.DisplayAlerts = True
End Sub

资源名称"字段给我带来了问题.我只需要显示以"* TBD"开头的资源名称，而排除名称中包含"ATG"的资源名称.到目前为止，我已经尝试了以下方法:

The "Resource name" field is giving me problems. I need to only show the resource names that begin with "*TBD" and exclude those that contain "ATG" in the name. I have so far tried the following:

Set objField = objTable.PivotFields("Resource name")
objField.Orientation = xlRowField
objField.Position = 5
objField.PivotFilters.Add xlCaptionContains, Value1:="TBD"
objField.PivotFilters.Add xlCaptionDoesNotContain, Value1:="ATG"
objField.AutoSort xlAscending, "Resource name"

返回运行时错误'1004':应用程序定义的错误或对象定义的错误

Which returns "Run-time error '1004': Application-defined or object-defined error

这并不是我真正需要的，因为我还需要过滤掉名称中没有"TBD"的那些，但是我也尝试过:

This isn't exactly what I need since I also need to filter out those without "TBD" in the name, but I have also tried:

Set objField = objTable.PivotFields("Resource name")
objField.Orientation = xlRowField
objField.Position = 5
objField.PivotItems("*ATG*").Visible = False
objField.AutoSort xlAscending, "Resource name"

哪个返回运行时错误'1004':无法获取PivotField类的PivotItems属性

Which returns "Run-time error '1004': Unable to get the PivotItems property of the PivotField class

我也尝试记录宏并根据我的代码检查结果.结果使用了我尝试过的PivotFilters.Add.记录的宏和我的代码之间的主要区别是使用PivotTableWizard，我开始怀疑这是否重要...我是新手，还记得吗?

I have also tried recording a macro and checking the results against my code. The results use PivotFilters.Add which I tried. The main difference between the recorded macro and my code is the use of PivotTableWizard and I'm starting to wonder if that matters...I'm new, remember?

关于如何解决此问题的任何想法?我正在使用Excel 2010，并花了数小时进行搜索，但我尝试过的所有方法均无济于事.在此先感谢您的帮助！

Any ideas on how to fix this? I'm using Excel 2010 and have spent hours searching for this, and nothing I've tried has worked. Thanks in advance for any help!!

推荐答案

不幸的是，您无法将相同类型的过滤器应用于PT中的一个字段. 此链接是一个不错的读物.

Unfortunatley, you cannot apply to of the same type of filters to one field in a PT. This link is a decent read on that.

要解决您的问题，您可以在上面的帖子中替换以下代码块:

To solve your problem you can can replace this block of code in your post above:

Set objField = objTable.PivotFields("Resource name")
objField.Orientation = xlRowField
objField.Position = 5
objField.AutoSort xlAscending, "Resource name"

使用以下代码:

'1) Filter on any thing that contains `TBD`
Set objField = objTable.PivotFields("Resource name")
objField.Orientation = xlRowField
objField.Position = 5
objField.PivotFilters.Add xlCaptionContains, Value1:="TBD"

'2) Loop through the items in the field and uncheck anything with `ATG` in the name
Dim pi As PivotItem
Dim i as Integer

For i = 1 To objField.PivotItems.Count
    If InStr(1, objField.PivotItems(i), "ATG") <> 0 Then
        objField.PivotItems(i).Visible = False
    End If
Next

objField.AutoSort xlAscending, "Resource name"

这篇关于使用宏过滤问题以在Excel 2010 VBA中创建数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！