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

Mikeykt

New Member
Joined
Feb 10, 2011
Messages
47
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Hi Michael,

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

Code:
     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
 
Upvote 0

Forum statistics

Threads
1,224,269
Messages
6,177,564
Members
452,784
Latest member
talippo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top