create multiple pivot table in vba

Question

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.

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

You should remove the On Error Goto Next and see what error you get. — Bob Phillips
Have you tried commenting out Set PTCache2... and only using PTCache1 for both pivot tables? — Mark Fitzgerald
@BobPhillips Unfortunately I am a Noob for Excel VBA, the error message means not much to me. These codes are actually what I found in YouTube with some minor adjustment to suit my data set. Thanks. — lukayl
Tell us what the error says, and on what line, maybe we can help with that. — Bob Phillips

Luuklag Luuklag · Accepted Answer · 2015-12-21T08:18:00

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.

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

Edit2: Fixed your code(hopefully)

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.

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

create multiple pivot table in vba

2 Answers