如何在VBA中创建数据透视表 [英] How to Create a Pivot Table in VBA

问题描述

我正在尝试创建数据透视表，但得到Invalid Procedure Call or Argument.

I'm trying to create a Pivot table, but getting Invalid Procedure Call or Argument.

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="rng", Version:=xlPivotTableVersion14).CreatePivotTable TableDestination:="rngB", TableName:="pvtReportA_B", DefaultVersion:=xlPivotTableVersion14

• rng(源)是一个范围，由大约20列和几千行组成.
• rngB(目标)是另一个工作表中的单个单元格
• rng (The source) is a range consisting of about 20 columns and a few thousand rows.
• rngB (The destination) is a single cell in a different worksheet

谁能告诉我我要去哪里错了?

Can anyone advise where I am going wrong?

我的错，我应该一直使用rngData而不是rng作为源.

My fault, I should have been using rngData and not rng as the Source.

    Set rng = wsA.Range("C14")
    Set rngData = Range(rng, rng.End(xlToRight))
    Set rngData = Range(rng, rng.End(xlDown))
    Set rngB = wsB.Range("C8")

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData, Version:=xlPivotTableVersion14).CreatePivotTable TableDestination:=rngB, TableName:="pvtReportA_B", DefaultVersion:=xlPivotTableVersion14

这很好地显示了数据透视表框架.

This brings up the PivotTable frame just fine.

推荐答案

在这种情况下，我使用了错误的范围对象，这导致Excel抛出拟合.

In this instance, I used the wrong range object, which caused Excel to throw a fit.

Set rng = wsA.Range("C14")
Set rngData = Range(rng, rng.End(xlToRight))
Set rngData = Range(rng, rng.End(xlDown))
Set rngB = wsB.Range("C8")

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData, Version:=xlPivotTableVersion14).CreatePivotTable TableDestination:=rngB, TableName:="pvtReportA_B", DefaultVersion:=xlPivotTableVersion14

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