数据透视表的移动位置 [英] Moving location of pivot table(s)

问题描述

如何移动数据透视表的位置，以确保其在刷新时不会与下面的另一个数据透视表重叠?

我有10个数据透视表，其列跨度始终等于A:E.
这些行将随着轴的刷新而动态更新.
我希望在每个新的数据透视表开始之前有2个空白行，因此我正在使用以下变通方法

I have 10 pivot tables whose column span is always equal to A:E.
The rows will dynamically update as the pivots refresh.
I want there to be 2 blank rows before each new pivot table starts so I am using the following workaround

(其他问题表明您无法在数据透视表刷新时直接插入行以阻止它们重叠，这是我要解决的根本问题/解决方法)

(other questions indicate you cannot directly insert rows as a pivot table refreshes to stop them from overlapping which is the root problem I am trying to curb/workaround)

1. 禁用自动计算
2. 遍历所有枢轴(pvt)
3. 用(LRow, 6)偏移枢轴....现在新列跨度是G:K
4. 刷新数据透视表(将在表调整大小时在下一个循环中更新LRow)
5. 下一个枢轴
6. 重新启用自动计算和删除列A:F，这意味着枢轴将回到跨越列A:E的原始起点，同时在每个枢轴之间保留2个空行.

1. Disable auto-calculation
2. Loop through all pivots (pvt)
3. Offset pivot by (LRow, 6) .... New column span is now G:K
4. Refresh Pivot (which will update LRow on next loop as the table resizes)
5. Next Pivot
6. Re-enable auto-calculate & delete Columns A:F which means the pivots will be back in their original starting point spanning columns A:E while leaving 2 empty rows in-between each pivot.

问题出在pvt.PivotTableWizard TableDestination:=Range("G" & lrow)上，它不移动数据透视表(或似乎无能为力).

The problem is with pvt.PivotTableWizard TableDestination:=Range("G" & lrow) which does not move the pivot table (or appear to do anything).

Sub MovePivots()

Dim pvt As PivotTable, LRow As Long

Application.Calculation = xlCalculationManual
    For Each pvt In PivotTables
        LRow = Range("G" & Rows.Count).End(xlUp).Offset(2).Row
        pvt.PivotTableWizard TableDestination:=Range("G" & LRow)
        pvt.PivotCache.Refresh
    Next pvt
Application.Calculation = xlCalculationAutomatic

    Range("A:F").EntireColumn.Delete

End Sub

推荐答案

我最终使用的内容:

这将刷新枢轴，同时保持所需的位置&每个枢轴之间的间距而不会因重叠而引发错误.要控制轴的处理顺序，请使用一个数组(以希望处理它们的顺序输入轴表名称).

What I ended up using:

This will refresh pivots while keeping the desired location & spacing between each pivot without throwing an error due to overlapping. To control the order that the pivots are handle, use an array (enter the pivot table names in the order in wish you want them to be handled).

在关闭屏幕更新的情况下，看起来轴心正在刷新，同时动态添加/删除行以保持所需的间距.

With screen updating toggled off, it just looks like the pivots are refreshing while dynamically adding/removing rows to keep desired spacing.

Option Explicit

Sub Refresh_Pivots()

Dim OTC As Worksheet: Set OTC = ThisWorkbook.Sheets("OTC")
Dim LRow As Long, i As Long, Pvts
Pvts = Array("PivotTable1", "PivotTable2", "PivotTable3", "PivotTable4", "PivotTable5")

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
    For i = LBound(Pvts) To UBound(Pvts)
        LRow = OTC.Range("N" & OTC.Rows.Count).End(xlUp).Offset(3).Row
        OTC.PivotTables(Pvts(i)).TableRange2.Cut OTC.Range("M" & LRow)
        OTC.PivotTables(Pvts(i)).PivotCache.Refresh
    Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

OTC.Range("B:L").EntireColumn.Delete

End Sub

感谢@BigBen& @jeffreyweir提供有用的解决方案/评论/链接

Thanks @BigBen & @jeffreyweir for useful solutions/comments/links

这篇关于数据透视表的移动位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！