Excel VBA数据透视表数据源问题

I have the following code...

我有以下代码…

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "page!R2C1:R1981C43", Version:=xlPivotTableVersion15).CreatePivotTable _
    TableDestination:="", TableName:="PivotTable5", DefaultVersion _
    :=xlPivotTableVersion15

How would i change it from "page" to pull for any current worksheet name that I want to attempt.

如何将它从“page”更改为要尝试的任何当前工作表名称。

For example if I run this on a worksheet named "pages" it gives me an error, how would I change it to accept any worksheet name?

例如，如果我在一个名为“pages”的工作表上运行它，它会给我一个错误，我将如何更改它以接受任何工作表名称?

2 个解决方案

#1

Just declare a variable and use that:

只需声明一个变量并使用它:

Dim myWorksheet as Worksheet
Set myWorksheet = Activesheet

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    myWorksheet & "!R2C1:R1981C43", Version:=xlPivotTableVersion15).CreatePivotTable _
    TableDestination:="", TableName:="PivotTable5", DefaultVersion _
    :=xlPivotTableVersion15

This is untested, but I'm pretty sure that should work.

这是未经测试的，但我很确定这是可行的。

#2

Dim wbpage as worksheet

set wbpage = thisworkbook.sheets(1) 'or whatever

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    wbpage.name & "!R2C1:R1981C43", Version:=xlPivotTableVersion15).CreatePivotTable _
    TableDestination:="", TableName:="PivotTable5", DefaultVersion _
    :=xlPivotTableVersion15

#1