在VBA中引用Excel 2012中的数据透视表 [英] Referencing pivot tables from excel 2012 in VBA

问题描述

我现在正在处理一个引用数据透视表的宏，但是我遇到的问题是我的引用仅针对特定的单元格范围，随着它的增长或扩展，我无法使其引用整个表.缩小取决于数据.我尝试了几种不同的方法，但是我没有使用VB的经验，所以不确定我是否完全理解语法...

这是我目前拥有的:

  Sheets("Loader").Select范围("C11").选择Application.CutCopyMode =假ActiveCell.FormulaR1C1 ="1"工作表(工作表").选择范围("U4:Y10").选择选择复制 

"U4:Y10"参考应该是数据透视表中的信息

解决方案

将Range.PivotTable.x用作数据透视表中值的范围，其中x是下面示例中显示的有效范围的一个选择.

例如在您的情况下，请使用单元格U4作为定位单元格，从中查找包含该数据透视表的数据透视表:Debug.Print Range("U4").PivotTable.TableRange1.Address(打印例如"U4:Y30")

所需的范围内的各种选择，例如:

  With Range("U4").PivotTable.DataBodyRange'只是值的范围.ColumnRange'列'labels'的范围.RowRange'行的范围'标签'.TableRange1'整个表格的范围(不包括页面字段).TableRange2'整个表格的范围(包括页面字段)结束于 

I am working on a macro right now that references a pivot table but the problem I'm having is that my reference is only for a specific cell range and I can't get it to reference the entire table as it grows or shrinks depending on the data. I've tried a few different things but I have no experience with VB so I'm not sure I entirely understand the syntax...

This is what I have currently:

Sheets("Loader").Select
Range("C11").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "1"
Sheets("Worksheet").Select
Range("U4:Y10").Select
Selection.Copy

The "U4:Y10" reference should be the information within the pivot table

解决方案

Use Range.PivotTable.x for the Range of the values within the pivot table where x is a choice from the valid ranges shown in my example below.

E.g. in your case use Cell U4 as the anchor cell from which to find the Pivot Table that contains it: Debug.Print Range("U4").PivotTable.TableRange1.Address (prints e.g. "U4:Y30")

The various choices over which range you want are such as:

With Range("U4").PivotTable
    .DataBodyRange 'Range of just the values
    .ColumnRange 'Range of Column 'labels'
    .RowRange 'Range of Row 'labels'
    .TableRange1 'Range of entire table (excluding page fields)
    .TableRange2 'Range of entire table (including page fields)
End With

这篇关于在VBA中引用Excel 2012中的数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！