在vba中创建多个pivot表

I have successfully created a pivot table by using VBA, but now I would like to create two pivot tables in two separate worksheets, 2 and 3. But my scripted VBA only creates one pivot table, even though I already set 2 Pivot table variables: PT1 and PT2, and 2 pivot cache variables: PTCache1 and PTCache2. The raw data is in worksheet 1, which I set as RawData.
Below is my script. Please help me out what I am missing here. Thanks.

我已经使用VBA成功地创建了一个pivot表，但是现在我想在两个独立的工作表(2和3)中创建两个pivot表。但是我用脚本编写的VBA只创建一个pivot表，尽管我已经设置了两个pivot表变量:PT1和PT2，以及两个pivot缓存变量:PTCache1和PTCache2。原始数据在工作表1中，我将它设置为RawData。下面是我的脚本。请帮我把这里缺的东西找出来。谢谢。

Private Sub CreatePivotTables()
Dim ReportC2 As Workbook, RawData As Worksheet, SanityA As Worksheet
Dim LastRow As Long, LastCol As Long, i As Long
Dim PTCache1 As PivotCache, PTCache2 As PivotCache, PT1 As PivotTable, PT2 As PivotTable

Set ReportC2 = Workbooks.Open(ScorecardAddr & "\" & C2Name)
Set RawData = ReportC2.ActiveSheet

With RawData
    Columns("A:A").Insert
    .Range("A1").Value = "Deal & SKU"
    LastRow = .Range("B" & Rows.Count).End(xlUp).Row
    LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column


    For i = 2 To LastRow
        .Range("A" & i) = .Range("F" & i).Value & "|" & .Range("P" & i).Value
    Next

End With

With RawData

    Set PTCache1 = ActiveWorkbook.PivotCaches.Create(xlDatabase, Range(Cells(1, 1), Cells(LastRow, LastCol)))
    Set PTCache2 = ActiveWorkbook.PivotCaches.Create(xlDatabase, Range(Cells(1, 1), Cells(LastRow, LastCol)))
End With

On Error Resume Next
With ReportC2

.Sheets.Add After:=.Sheets(.Sheets.Count), Count:=2
.Sheets(2).Name = "C2Pivot-Transactional"

    Set PT1 = .Sheets(2).PivotTables.Add(PTCache1, Range("A7"), "C2PT1")

    'put the fields in the pivot table
    With PT1
        .PivotFields("Deal & SKU").Orientation = xlRowField
        .PivotFields("quantity").Orientation = xlDataField
        .PivotFields("GrossSellto").Orientation = xlDataField
        .PivotFields("Total BDD Rebate").Orientation = xlDataField
        .PivotFields("Total FLCP Rebate").Orientation = xlDataField
    End With
End With

With ReportC2

    .Sheets(3).Name = "C2Pivot-Reseller"

    Set PT2 = .Sheets(3).PivotTables.Add(PTCache2, Range("A7"), "C2PT2")

    'put the fields in the pivot table
    With PT2
        .PivotFields("Reseller ID").Orientation = xlRowField
        .PivotFields("GrossSellto").Orientation = xlDataField
        .CalculatedFields.Add "BDD + FLCP", "= 'Total BDD Rebate' + 'Total FLCP Rebate'"
        .PivotFields("BDD + FLCP").Orientation = xlDataField
    End With

End With

End Sub

2 个解决方案

#1

First of all try not using the .Select rather try to explicitly refernce your workbooks and sheets. Because of this you'r pivottables both get added at Range("A7"), so on the same place. Try referencing to start with WB.WS.Range, where WB is a variable that stores your workbook and WS a variable that stores your WS.

首先，不要使用. select，而要显式地引用工作簿和工作表。因此，您的数据透视表都被添加到Range(“A7”)中，所以在相同的位置。尝试从WB.WS开始引用。Range，其中WB是存储工作簿的变量，WS是存储WS的变量。

Edit: as a reference, here a question, and answers, on how to avoid using .Select: How to avoid using Select in Excel VBA macros

编辑:作为参考，这里有一个问题和答案，关于如何避免使用。Select:如何避免在Excel VBA宏中使用Select

Edit2: Fixed your code(hopefully)

Edit2:固定你的代码(希望)

Edit3: Add-back with...end with, and put the two pivotcaches in the With loop, also remove PT2 variable as duplicate, and lastly merge the "With ReportC2 between the two PT table addon. And it works.

Edit3:添加回来……最后，将两个数据透视缓存放在with循环中，也将PT2变量删除为duplicate，最后在两个PT表addon之间合并“with ReportC2”。和它的工作原理。

Private Sub CreatePivotTables()
Dim ReportC2 As Workbook, RawData As Worksheet, SanityA As Worksheet
Dim LastRow As Long, LastCol As Long, i As Long
Dim PTCache1 As PivotCache, PTCache2 As PivotCache, PT As PivotTable

Set ReportC2 = Workbooks.Open(ScorecardAddr & "\" & C2Name)
Set RawData = ReportC2.Worksheets(1)

With RawData
    Columns("A:A").Insert
    .Range("A1").Value = "Deal & SKU"
    LastRow = .Range("B" & Rows.Count).End(xlUp).Row
    LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column


    For i = 2 To LastRow
        .Range("A" & i) = .Range("F" & i).Value & "|" & .Range("P" & i).Value
Next

End With
with RawData
    Set PTCache1 = ActiveWorkbook.PivotCaches.Create(xlDatabase, Range(Cells(1, 1), Cells(LastRow, LastCol)))
    Set PTCache2 = ActiveWorkbook.PivotCaches.Create(xlDatabase, Range(Cells(1, 1), Cells(LastRow, LastCol)))
End With

On Error Resume Next
With ReportC2

.Sheets.Add After:=.Sheets(.Sheets.Count), Count:=2
.Sheets(2).Name = "C2Pivot-Transactional"

    Set PT = .Sheets(2).PivotTables.Add(PTCache1, Worksheets(2).Range("A7"), "C2PT1")

    'put the fields in the pivot table
    With PT
        .PivotFields("Deal & SKU").Orientation = xlRowField
        .PivotFields("quantity").Orientation = xlDataField
        .PivotFields("GrossSellto").Orientation = xlDataField
        .PivotFields("Total BDD Rebate").Orientation = xlDataField
        .PivotFields("Total FLCP Rebate").Orientation = xlDataField
    End With

    .Sheets(3).Name = "C2Pivot-Reseller"

    Set PT2 = .Sheets(3).PivotTables.Add(PTCache2, Worksheets(3).Range("A7"), "C2PT2")

    'put the fields in the pivot table
    With PT2
        .PivotFields("Reseller ID").Orientation = xlRowField
        .PivotFields("GrossSellto").Orientation = xlDataField
        .CalculatedFields.Add "BDD + FLCP", "= 'Total BDD Rebate' + 'Total FLCP Rebate'"
        .PivotFields("BDD + FLCP").Orientation = xlDataField
    End With

End With

End Sub

#2

This works for me on assumed data. I ditched PTCache2, one is sufficient for the same source.

这对我来说适用于假设的数据。我抛弃了PTCache2，一个就足够了。

Private Sub CreatePivotTables()
Dim ReportC2 As Workbook, RawData As Worksheet, SanityA As Worksheet
Dim LastRow As Long, LastCol As Long, i As Long
Dim PTCache1 As PivotCache, PT As PivotTable, Pt2 As PivotTable

    Set ReportC2 = ActiveWorkbook 'Workbooks.Open(ScorecardAddr & "\" & C2Name)
    Set RawData = ReportC2.Worksheets(1)

    With RawData

        .Columns("A:A").Insert
        .Range("A1").Value = "Deal & SKU"
        LastRow = .Range("B" & .Rows.Count).End(xlUp).Row
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column


        For i = 2 To LastRow
            .Range("A" & i) = .Range("F" & i).Value & "|" & .Range("P" & i).Value
        Next

        Set PTCache1 = ActiveWorkbook.PivotCaches.Create(xlDatabase, .Range(.Cells(1, 1), .Cells(LastRow, LastCol)))
    End With

    With ReportC2

    .Sheets.Add After:=.Sheets(.Sheets.Count), Count:=2
    .Sheets(2).Name = "C2Pivot-Transactional"

        Set PT = .Sheets(2).PivotTables.Add(PTCache1, Worksheets(2).Range("A7"), "C2PT1")

        'put the fields in the pivot table
        With PT
            .PivotFields("Deal & SKU").Orientation = xlRowField
            .PivotFields("quantity").Orientation = xlDataField
            .PivotFields("GrossSellto").Orientation = xlDataField
            .PivotFields("Total BDD Rebate").Orientation = xlDataField
            .PivotFields("Total FLCP Rebate").Orientation = xlDataField
        End With

        .Sheets(3).Name = "C2Pivot-Reseller"

        Set Pt2 = .Sheets(3).PivotTables.Add(PTCache1, Worksheets(3).Range("A7"), "C2PT2")

        'put the fields in the pivot table
        With Pt2
            .PivotFields("Reseller ID").Orientation = xlRowField
            .PivotFields("GrossSellto").Orientation = xlDataField
            .CalculatedFields.Add "BDD + FLCP", "= 'Total BDD Rebate' + 'Total FLCP Rebate'"
            .PivotFields("BDD + FLCP").Orientation = xlDataField
        End With
    End With
End Sub

#1