烦人的烦恼 [英] Pivottable woes

问题描述

好像所有其他人的例子都像我在代码中已经有的一样。但在我的情况下，什么也没有解决问题。所以这里是完整的代码以及一个休息，以显示我在哪里遇到问题：

  Sub CreatePivot（）
'定义RngTarget和RngSource作为范围类型变量
 Dim RngTarget As Range 
 Dim RngSource As Range 
 Dim intLastCol As Integer 
 Dim intLCPivot As Integer 
 Dim intLRPivot As Integer 
 Dim intCntrCol As Integer 
 Dim intX，intY As Integer 
 Dim ws1，ws2 As Worksheet 
 Dim pt As PivotTable 
 Dim cf As FormatCondition 
 
设置ws1 = ThisWorkbook.Sheets（Sheet1）
设置ws2 = ThisWorkbook.Sheets（Sheet2）
 ws2.Cells.Clear 
 
'RngTarget是数据透视表将被创建（即：Sheet2，单元格B3）
设置RngTarget = ws2.Range（B3）
 
'RngSource定义将用于创建数据透视表$ b的范围$ b设置RngSource = ws1.UsedRan ge 
 
'选择范围
 ws1.Select'不知道为什么需要这个，但如果省略则为1004 
 RngSource.Select 
 
'复制范围进入剪贴板
 RngSource.Copy 
 
'使用上面定义的RngSource创建一个新的数据透视表
 ActiveWorkbook.PivotCaches.Create（xlDatabase，RngSource）.CreatePivotTable RngTarget，PivotB3 
设置pt = RngTarget.PivotTable 
 
这是问题所在。创建可靠性后，如果我看到sheet2，我会看到
只有向导。如下所示：http://wikisend.com/download/617932/ptwizard.jpg 
我需要将所有字段自动选中以包含在
中。我没有能力测试任何东西，尽管它编译没有
一个问题。 
 
'从pivotottable 
获取最后一个列和行intLCPivot = pt.DataBodyRange.Columns（pt.DataBodyRange.Columns.Count）.Column 
 intLRPivot = pt.DataBodyRange。 Rows（pt.DataBodyRange.Rows.Count）.Row 
 
'从数据表中获取最后使用的列
 intLastCol = RngSource.Columns（RngSource.Columns.Count）.Column 
 
'选择数据透视表，以便我们可以应用条件格式
 pt.PivotSelect，xlDataAndLabel，True 
 
这也会导致错误。我需要能够从Sheet1：B3获取条件格式，并将其
'应用于数据透视表报表中的所有数据单元格，而不是像现在这样的格式进行硬编码。 
 
'cf = ws1.Range（B3）。FormatCondition 
 
 ws2.Select 
对于intX = 1对intLCPivot 
对于intY = 1 intLRPivot 
 ws2.Cells（4，intCntrCol）.Select'选择当前Sum列
 Selection.FormatConditions.Add类型：= xlCellValue，运算符：= xlLess，Formula1：== 5000'设置条件格式小于5000 
 Selection.FormatConditions（Selection.FormatConditions.Count）.SetFirstPriority'优先于任何其他格式
 With Selection.FormatConditions（1）.Font'将Font属性用于下一个操作
 .ThemeColor = xlThemeColorLight1'将其设置为默认值（如果不符合条件）
 .TintAndShade = 0'与上述相同
 End With 
 With Selection.FormatConditions （1）。内部
 .PatternColorIndex = xlAutomatic 
 .Color = 65535'将背景颜色设置为黄色
 .TintAndShade = 0 
结束
 Selection.FormatConditions（1）.StopIfTrue = False 
'Selection.FormatConditions（1 ）.ScopeType = xlFieldsScope'将格式应用于与xxxx的总和匹配的所有行
 Next intY 
 Next intX 
 End Sub 
  pre> 
 
解决方案
如果将其添加到脚本中以代替error'd行，您将获得所有的名称和添加然后到枢纽：
 对于每个p在pt.PivotFields 
 Debug.Print p.Name 
 pt.AddDataField p ,, xlSum 
下一个
  
我相信这会让你过去线。 ：）
 
It seems like all of the examples from other people are just like what I already have in my code. But in my case, nothing resolves the problem. So here is the complete code along with a break to show where I am having a problem:
    Sub CreatePivot()
        ' Define RngTarget and RngSource as Range type variables
        Dim RngTarget As Range
        Dim RngSource As Range
        Dim intLastCol As Integer
        Dim intLCPivot As Integer
        Dim intLRPivot As Integer
        Dim intCntrCol As Integer
        Dim intX, intY As Integer
        Dim ws1, ws2 As Worksheet
        Dim pt As PivotTable
        Dim cf As FormatCondition

        Set ws1 = ThisWorkbook.Sheets("Sheet1")
        Set ws2 = ThisWorkbook.Sheets("Sheet2")
        ws2.Cells.Clear

        ' RngTarget is where the PivotTable will be created (ie: Sheet2, Cell B3)
        Set RngTarget = ws2.Range("B3")

        ' RngSource defines the Range that will be used to create the PivotTable
        Set RngSource = ws1.UsedRange

        ' Select the Range
        ws1.Select  ' Not sure why this is needed, but 1004 if omitted
        RngSource.Select

        ' Copy the Range into the clipboard
        RngSource.Copy

        ' Create a new PivotTable using the RngSource defined above
        ActiveWorkbook.PivotCaches.Create(xlDatabase, RngSource).CreatePivotTable RngTarget, "PivotB3"
        Set pt = RngTarget.PivotTable

    '' This is where the the problem is. After the pivottable is created, if I look at sheet2, I see
    '' only the wizard. Like this: http://wikisend.com/download/617932/ptwizard.jpg
    '' I need to have all of the fields automatically selected to be included in
    '' the report. I haven't been able to test anything beyond this, although it compiles without
    '' a problem.

        ' Get the last col and row from the pivottable
        intLCPivot = pt.DataBodyRange.Columns(pt.DataBodyRange.Columns.Count).Column
        intLRPivot = pt.DataBodyRange.Rows(pt.DataBodyRange.Rows.Count).Row

        ' Get the last used column from the data table
        intLastCol = RngSource.Columns(RngSource.Columns.Count).Column

        ' Select the Pivot table so we can apply the conditional formats
        pt.PivotSelect "", xlDataAndLabel, True

    '' This also causes an error. I need to be able to get the conditional format from Sheet1:B3 and apply it
    '' to all Data cells in the PivotTable Report, instead of hardcoding the format like it is now.

        'cf = ws1.Range("B3").FormatCondition

        ws2.Select
        For intX = 1 To intLCPivot
            For intY = 1 To intLRPivot
                ws2.Cells(4, intCntrCol).Select ' Select the current Sum column
                Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=5000" ' Set conditional format to less than 5000
                Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority ' Take priority over any other formats
                With Selection.FormatConditions(1).Font ' Use the Font property for the next operations
                    .ThemeColor = xlThemeColorLight1 ' Set it to the default (if it does not meet the condition)
                    .TintAndShade = 0 ' Same as above
                End With
                With Selection.FormatConditions(1).Interior
                    .PatternColorIndex = xlAutomatic
                    .Color = 65535 ' Set the background color to Yellow
                    .TintAndShade = 0
                End With
                Selection.FormatConditions(1).StopIfTrue = False
                'Selection.FormatConditions(1).ScopeType = xlFieldsScope ' Apply the format to all rows that match "Sum of xxxx"
            Next intY
        Next intX
    End Sub

解决方案
If you add this to your script in place of the error'd lines you'll get all their names and add then to the pivot:
For Each p In pt.PivotFields
    Debug.Print p.Name
    pt.AddDataField p,,xlSum
Next
I'm sure this will get you over the  line. :)

                        
这篇关于烦人的烦恼的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！