将数据透视表值复制到新的工作表vba

问题描述

我正在尝试使用VBA将数据透视表值复制到新工作表中.我收到了运行时错误5.这是我的代码

I am trying to copy Pivot table values into a new sheet using VBA. I am getting a runtime error 5. Here is my code

Sub test()

    Dim shtTarget As Worksheet, pvtSht As Worksheet
    Dim pc As PivotCache
    Dim pt As PivotTable
    Dim field As PivotField
    Dim rngSource As Range
    Dim wb As Workbook: Set wb = ThisWorkbook

    With wb
        Set rngSource = .Sheets(2).Range("A5").CurrentRegion
        Set shtTarget = .Sheets.Add(After:=.Sheets(.Sheets.Count))

        Set pc = .PivotCaches.Create(xlDatabase, rngSource.Address(False, False, xlA1, xlExternal))
Set pt = pc.CreatePivotTable(shtTarget.Range("A1"), "PivotTable3", , xlPivotTableVersion14)
 End With

With pt.PivotFields("Concatenate")
.Orientation = xlRowField
.Position = 1
End With

pt.AddDataField pt.PivotFields("SCREEN_ENTRY_VALUE"), "Sum of SCREEN_ENTRY_VALUE", xlSum

With wb
Set pvtSht = .Sheets.Add(After:=.Sheets(.Sheets.Count))
pvtSht.Name = "Sum of Element Entries"

    pt.TableRange2.Copy
    pvtSht.Range("A1").PasteSpecial xlPasteValues
End With

End Sub

我的数据透视表的范围是A5:I266.设置pt = pc.CreatePivotTable ...上出现代码错误...

My pivot table is in a range of A5:I266. The code errors out on Set pt = pc.CreatePivotTable...

推荐答案

替换:

Set pt = pc.CreatePivotTable(shtTarget.Range("A1"), "PivotTable3", , xlPivotTableVersion14)

具有:

Set pt = pc.CreatePivotTable(shtTarget.Range("A1"), "PivotTable3")

这篇关于将数据透视表值复制到新的工作表vba的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！