CODEWITHSUNDEEP
excelpivot-tablevba
Uniasus
Uniasus

Reputation: 107

Collapsing One Pivot Field Among Many

I have a pivot table with a few fields, and I want only one pivot field collapsed. I can't seem to make it work, and so would like some help.

Dim pt As PivotTable
Dim pr As Range
Dim pc As PivotCache


Set pr = dataSht.UsedRange
Set pc = wb.PivotCaches.Create(xlDatabase, SourceData:=pr)
Set pt = pc.createPivotTable(wb.Worksheets(shtName).Range("A1"), strPivotName)


With pt

.PivotFields("One").Orientation = xlRowField
.PivotFields("One").Subtotals(1) = False
.PivotFields("Two").Orientation = xlRowField
.PivotFields("Two").Subtotals(1) = False
.PivotFields("Three").Orientation = xlRowField
.PivotFields("Three").Subtotals(1) = False
.PivotFields("Four").Orientation = xlRowField
.PivotFields("Four").Subtotals(1) = False


With .PivotFields("Sum of $$$ (000)")
        .Orientation = xlDataField
        .Caption = "% Total"
        .Calculation = xlPercentOfParent
        .BaseField = "Two"
        .NumberFormat = "0.00%"

End With

I'm looking to collapse the last field, Four. Adding .PivotFields("Four").ShowDetail = False to the end of my first With gives me an application-defined or object-defined error. "ShowDetails" results in the same.

Alternatively, I've tried using DrillTo, though I didn't quite understand it, in the following capacity after End With:

Dim pi as PivotItem
pi.DrillTo Field:= "Three"

Again, I get an error. Object variable or with block variable not set.

Still wrapping my head around VBA, so if someone could help me out that'd be wonderful!

Upvotes: 3

Views: 1406

Answers (1)

Uniasus
Uniasus

Reputation: 107

Figured it out! So for those users in the future who might have a similar problem:

I had to go up a level and specifically identify the pivot table because it just wasn't working in with pt

So, after the above code in my question, I added 

pt.TableStyle2 = "PivotStyleMedium3"
pt.PivotFields("Three").ShowDetail = False

end With

Why it wouldn't work in my first with block, I have no idea, but at least it's working. My guess would be something along the lines of not being able to manipulate a pivot table until it's fully created. Maybe. shrugs

Upvotes: 1

Related Questions