Creating Pivot Tables with VBA and putting in a tabular form with no subtotals

[Mikeykt]

Hi

I have created the code below to create a Pivot Table using VBA, so that it populates from a list box option.

I would prefer if it didn't show any subtotals and was in a tabular form. Can anyone see where I have gone wrong and it keeps dropping off at that point for each row field.

Thanks

Michael

CODE
Sub trail()
'
' trail Macro
'
'
Dim wksPivot As Worksheet
Dim wksData As Worksheet
Dim pc As PivotCache
Dim PT As PivotTable
Set wksPivot = Sheets("PIVOT")
Set wksData = Sheets("Full Data")
wksPivot.UsedRange.Clear
With wksData
Set pc = .Parent.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'" & .Name & "'!" & .Range("A1").CurrentRegion.Address(True, True, xlR1C1))
Set PT = pc.CreatePivotTable(TableDestination:="'" & wksPivot.Name & "'!R3C1", TableName:="")
End With
With PT
With .PivotFields("Recruitment Source")
.Orientation = xlPageField
.Position = 1
End With
With .PivotFields("Mailing Code")
.Orientation = xlPageField
.Position = 1
End With
With .PivotFields("List Source Description").Subtotals = Array _
(False, False, False, False, False, False, False, False, False, False, False, False). _
LayoutForm = xlTabular
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("Merge").Subtotals = Array _
(False, False, False, False, False, False, False, False, False, False, False, False). _
LayoutForm = xlTabular
.Orientation = xlRowField
.Position = 2
End With
With .PivotFields("Channel").Subtotals = Array _
(False, False, False, False, False, False, False, False, False, False, False, False). _
LayoutForm = xlTabular
.Orientation = xlRowField
.Position = 3
End With
With .PivotFields("Campaign Name").Subtotals = Array _
(False, False, False, False, False, False, False, False, False, False, False, False). _
LayoutForm = xlTabular
.Orientation = xlRowField
.Position = 4
End With
With .PivotFields("Segment").Subtotals = Array _
(False, False, False, False, False, False, False, False, False, False, False, False). _
LayoutForm = xlTabular
.Orientation = xlRowField
.Position = 5
End With
With .PivotFields("Agency").Subtotals = Array _
(False, False, False, False, False, False, False, False, False, False, False, False). _
LayoutForm = xlTabular
.Orientation = xlRowField
.Position = 6
End With
.AddDataField .PivotFields("RG Donors"), "Count of RG Donors", xlCount
With .PivotFields("Count of RG Donors")
.Caption = "Sum of RG Donors"
.Function = xlSum
End With
.AddDataField .PivotFields("RG Transaction Value"), "Count of RG Transaction Value", xlCount
With .PivotFields("Count of RG Transaction Value")
.Caption = "Sum of RG Transaction Value"
.Function = xlSum
End With
.AddDataField .PivotFields("Cash Donors"), "Count of Cash Donors", xlCount
With .PivotFields("Count of Cash Donors")
.Caption = "Sum of Cash Donors"
.Function = xlSum
End With
.AddDataField .PivotFields("Cash Transaction Value"), "Count of Cash Transaction Value", xlCount
With .PivotFields("Count of Cash Transaction Value")
.Caption = "Sum of Cash Transaction Value"
.Function = xlSum
End With
.Name = "APPDATA"
End With

Worksheets("PIVOT").Select
With Worksheets("Snapshot Data")
With .DropDowns("Recruitment")
ActiveSheet.PivotTables("APPDATA").PivotFields("Recruitment Source").CurrentPage = .List(.ListIndex)
End With
With .DropDowns("Mailing")
ActiveSheet.PivotTables("APPDATA").PivotFields("Mailing Code").CurrentPage = .List(.ListIndex)
End With


End With

End Sub

 

[Jerry Sullivan]

Hi Michael,

Try using this approach to handling the SubTotals and Tabular layout....

     With PT
       .RowAxisLayout xlTabularRow
        With .PivotFields("Recruitment Source")
            .Orientation = xlPageField
            .Position = 1
        End With
        With .PivotFields("Mailing Code")
            .Orientation = xlPageField
            .Position = 2
        End With
        With .PivotFields("List Source Description")
            .Subtotals(1) = False
            .Orientation = xlRowField
            .Position = 1
        End With
        With .PivotFields("Merge")
            .Subtotals(1) = False
            .Orientation = xlRowField
            .Position = 2
        End With

 

[Mikeykt]

Thanks Jerry

That worked perfectly.

Michael