VBA - 从数据透视表中选择单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18317754/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA - Select Cells from Pivot Table
提问by Nat Aes
I am quite a novice at VBA and first time posting, so please bear with me.
我是 VBA 的新手，也是第一次发帖，所以请耐心等待。
I am trying to copy data from a pivot table and paste value into a separate worksheet. I need to leave the macro as flexible as possible to cater for any potential future changes to the pivot. In essence I would like to copy (for example) A6:E77, but this row range may need to change (so to A6:E84, rather than A6:G77) if the underlying data changes. I can select all the vertical non-blank cells in a discontinguous data set using:
我正在尝试从数据透视表复制数据并将值粘贴到单独的工作表中。我需要让宏尽可能灵活，以适应未来可能对支点进行的任何更改。本质上，我想复制（例如）A6:E77，但如果基础数据发生变化，则此行范围可能需要更改（因此更改为 A6:E84，而不是 A6:G77）。我可以使用以下方法选择不连续数据集中的所有垂直非空白单元格：
Range("A6", Range("A1048576").End(xlUp)).Select
However how can I then select all the cells in columns B:E that are alongside the cells chosen in column A?
但是，我如何才能选择 B:E 列中与 A 列中选择的单元格相邻的所有单元格？
I have tried using the following code, but it seems to ignore the End.(xlUp) command.
我尝试使用以下代码，但它似乎忽略了 End.(xlUp) 命令。
Range("A6:E1048576", Range("A6:E1048576").End(xlUp)).Select
Any ideas? I appreciate the help :)
有任何想法吗？我感谢帮助:)
回答by AndASM
You can access pivot tables through their PivotTableobjects in the PivotTables collection on the relevant Worksheet object.
您可以通过相关 Worksheet 对象上的 PivotTables 集合中的 PivotTable 对象访问数据透视表。
For example a pivot table on your first sheet named PivotTable1could be accessed as Sheet1.PivotTables("PivotTable1").
例如，您的第一个工作表上的数据透视表PivotTable1可以作为Sheet1.PivotTables("PivotTable1").
These PivotTable objects have useful properties such as DataBodyRange, DataLabelRange, ColumnRange, RowRange, PageRange, etc. These ranges are dynamically updated to point to the relevant parts of the pivot table.
这些数据透视表对象具有有用的属性，例如 DataBodyRange、DataLabelRange、ColumnRange、RowRange、PageRange 等。这些范围会动态更新以指向数据透视表的相关部分。
There are also properties such as RowFields, ColumnFields, and DataFields that allow you to inspect the current structure of the PivotTable.
还有一些属性，例如 RowFields、ColumnFields 和 DataFields，可让您检查数据透视表的当前结构。
Without knowing more about what parts of the pivot you are trying to copy I can't give more specific advice. But you can take a look at the documentation for the PivotTable object here.
如果不了解您要复制的枢轴的哪些部分，我无法提供更具体的建议。但是您可以在此处查看 PivotTable 对象的文档。
