CODEWITHSUNDEEP
excelexcel-2010pivot-tablevba
rohrl77
rohrl77

Reputation: 3337

Pivot Table Formatting returns error

I am working on a macro in which I am putting together PivotTables.

My Problem: I can not get the calculation part to work. I get an error everytime I run the code at the stage where PivotField(18) is supposed to calculate the .Function=xlCount. The error I get is "1004 select method of range class failed".

All the examples I've found online and in the MS Help section are structured the way I have set up my code. What am I missing???

Any help is highly appreciated...

I've been working on this the better part of the day. The following page (Link) has been super helpful, but hasn't helped me in cracking this particular problem.

' Start of PivotSpecific Formatting
Set pt = ActiveSheet.PivotTables("PT" & wsNewWorksheetName)
With pt
    ' Prevent AutoFormatting of Columns
    .HasAutoFormat = False
    ' Hide Field Headers
    .DisplayFieldCaptions = False
    .TableStyle2 = "PivotStyleMedium2"
    ' Apply calculation to numbers and change Caption
    With .PivotFields(18)
        .Caption = "Demand"
        .Function = xlCount
        .NumberFormat = "#,##0"
    End With
    With .PivotFields(15)
        .Caption = "Lowest OP"
        .Function = xlMin
        .NumberFormat = "#,##0.00"
    End With
End With

Update 1: Screenshot of Pivot Table Pivot Table Screenshot

Update 2: Tested Code from bonCodigo Here is the code adjusted to my workbook. With this I get a run time error 91 on the line: Set pvField = pvTable.PivotFields(18)

Dim wkSheet As Worksheet
Dim pvTable As PivotTable
Dim pvField As PivotField

Set wkSheet = ThisWorkbook.Worksheets(wsNewWorksheetName)
Set pvTble = wkSheet.PivotTables("PT" & wsNewWorksheetName)
Set pvField = pvTable.PivotFields(18)

With pvField
     .Caption = "Demand"
     .Function = xlCount
     .orientation = xlDataField
     .NumberFormat = "#,##0"
End With

Set pvField = Nothing
Set pvTable = Nothing
Set wkSheet = Nothing

Upvotes: 1

Views: 2018

Answers (1)

bonCodigo
bonCodigo

Reputation: 14361

Try this please and comment with your results. So we can see from there.

Option Explicit

'-- whatever other code you may have... 

Dim wkSheet as Worksheet
Dim pvTable as PivotTable
Dim pvField as PivotField

Set wkSheet = ThisWorkbook.Worksheets("Sheet1") '-- user proper sheet name explicitly
Set pvTble = wkSheet.PivotTables("PivotTable1") 
   ' or wkSheet.PivotTables(1) '-- use pivot table name or index
Set pvField = pvTable.PivotFields("SomeField") '-- use pivot field name or index

' -- do whatever you want to do with the pivot table

' -- do what you need to do with the pivot field
With pvField
     .Caption = "Demand"
     .Orientation = xlDataField '--<< THIS could well be the culprit
     .Function = xlCount
     .NumberFormat = "#,##0"
End With

Set pvField = Nothing
Set PvTable = Nothing
Set wkSheet = Nothing

Since you have more than one field to format, I recommend you to use a loop if they are consecutive using DataFields Index

e.g. pvTable.DataFields(1).Function = xlCount

Otherwise you will have to define them separately. It is always best to assign an variable as a reference to the object that you intend to interact. :)

Upvotes: 1

Related Questions