VBA-从数据透视表中选择单元格 [英] VBA - Select Cells from Pivot Table

问题描述

我是VBA和首次发布的新手，所以请多多包涵.

I am quite a novice at VBA and first time posting, so please bear with me.

我正在尝试从数据透视表中复制数据并将值粘贴到单独的工作表中.我需要使宏尽可能灵活，以适应将来对轴的任何潜在更改.本质上，我想复制(例如)A6:E77，但是如果基础数据发生更改，则可能需要更改此行范围(因此更改为A6:E84，而不是A6:G77).我可以使用以下方法在不连续的数据集中选择所有垂直非空白单元格:

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:

    Range("A6", Range("A1048576").End(xlUp)).Select

但是，我如何才能选择B:E列中与A列中所选单元格并排的所有单元格?

However how can I then select all the cells in columns B:E that are alongside the cells chosen in column A?

我尝试使用以下代码，但似乎忽略了End.(xlUp)命令.

I have tried using the following code, but it seems to ignore the End.(xlUp) command.

    Range("A6:E1048576", Range("A6:E1048576").End(xlUp)).Select

有什么想法吗?感谢您的帮助:)

Any ideas? I appreciate the help :)

推荐答案

您可以通过其例如，第一个工作表上名为PivotTable1的数据透视表可以作为Sheet1.PivotTables("PivotTable1")访问.

For example a pivot table on your first sheet named PivotTable1 could be accessed as Sheet1.PivotTables("PivotTable1").

这些数据透视表对象具有有用的属性，例如DataBodyRange，DataLabelRange，ColumnRange，RowRange，PageRange等.这些范围会动态更新以指向数据透视表的相关部分.

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.

还有诸如RowFields，ColumnFields和DataFields之类的属性，这些属性使您可以检查数据透视表的当前结构.

There are also properties such as RowFields, ColumnFields, and DataFields that allow you to inspect the current structure of the PivotTable.

在不了解您要复制的枢轴的哪些部分的情况下，我无法提供更具体的建议.但是您可以查看

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.

这篇关于VBA-从数据透视表中选择单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！