excel - VBA:在新工作表上创建数据透视表

Question

我正在尝试在数据源的单独工作表上创建数据透视表。我遇到的问题是在与 PivotCache 不同的页面上创建数据透视表，因为我收到运行时错误“1004”，而我可以将数据透视表粘贴到同一张纸上没问题。我目前拥有的代码如下所示:

    Sub PivotTable()

ThisWorkbook.Worksheets("FG REPORT").Activate

Dim ws As Worksheet
Dim pc As PivotCache
Dim pt As PivotTable
Dim pf As PivotField
Dim pf2 As PivotField
Dim pf3 As PivotField


Set pc = ThisWorkbook.PivotCaches.Create( _
                                        SourceType:=xlDatabase, _
                                        SourceData:=Sheet4.Name & "!" & Sheet4.Range("A1").CurrentRegion.Address, _
                                        Version:=xlPivotTableVersion15)


Set ws = ThisWorkbook.Sheets("Pivot")
ws.Activate
Cells.Select
Cells.Clear
Range("A1").Select


Set pt = pc.CreatePivotTable( _
    TableDestination:=ActiveCell, _
    TableName:="AgedPivot")


'Set Pivot Field variables

Set pf = pt.PivotFields("Description")
Set pf2 = pt.PivotFields("Age Range")
Set pf3 = pt.PivotFields("OH Qtys")

pf.Orientation = xlRowField
pf2.Orientation = xlPageField
pf3.Orientation = xlDataField


End Sub

Answer 1

尝试在工作表名称之前和之后连接一个单引号，因为名称包含一个空格:

SourceData:="'" & Sheet4.Name & "'!" & Sheet4.Range("A1").CurrentRegion.Address