Changing Pivot Table Fields Programmatically

T

Todd Huttenstine

Hey guys

I have allot of possible fields in my pivot table, however
I only need to display 3 fields at a time. I have
recorded a macro that will programmatically change the
fields, however I am running into a problem. For example
I have a Pivot Table that is currently displaying Average
of % Overdue19, Sum of On Time19, and Sum of Total19 in
the data part of the table. I have recorded a macro that
will remove those fields from the data part of the table
and in place, display the Average of % Overdue, Sum of On
Time, and Sum of Total instead. This code works perfectly
IF the current fields in the data part of table are
Average of % Overdue19, Sum of On Time19, and Sum of
Total19. If there are any other field names that are in
the data part of the field, then I get the error "Run-time
error '1004': Unable to get the PivotFields property of
the PivotTable class."

The reason I get this error is because the fields cannot
be found in the data part of the table because other field
names are there instead of the fields specified to
remove. How do I make the code say hide whatever fields
are currently in the data part of the pivot table and then
display whatever I specifiy in the code?

Below is my code as it is now:

Sheets("Pivot WOW REPS").Select
Range("B8").Select
ActiveSheet.PivotTables("PivotTable1").AddFields
RowFields:=Array( _
"Employee Name", "Data"), PageFields:=Array
("Department", "AD", "Supervisor")
ActiveSheet.PivotTables("PivotTable1").PivotFields
("Sum of Total19"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields
("Average of % Overdue19"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields
("Sum of On Time19"). _
Orientation = xlHidden
With ActiveSheet.PivotTables("PivotTable1").PivotFields
("On Time")
.Orientation = xlDataField
.Caption = "Sum of On Time"
.Position = 1
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields
("% Overdue")
.Orientation = xlDataField
.Caption = "Average of % Overdue"
.Position = 2
.Function = xlAverage
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields
("Total")
.Orientation = xlDataField
.Caption = "Sum of Total"
.Function = xlSum
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields
("Data").PivotItems( _
"Sum of On Time").Position = 1
ActiveSheet.PivotTables("PivotTable1").PivotFields
("Data").PivotItems( _
"Average of % Overdue").Position = 2
ActiveSheet.PivotTables("PivotTable1").PivotFields
("Data").PivotItems( _
"Sum of Total").Position = 3

Thank you

Todd Huttenstine
 
T

Todd Huttenstine

Hey again!

Well I figured it out. I used the following code:

Set pvttable = Worksheets("Pivot WOW REPS").PivotTables
("PivotTable1")
For Each pvtfield In pvttable.DataFields
pvtfield.Orientation = xlHidden
Next pvtfield
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Pivot Table Error 1
pivot table macro with a filtering variable 3
macro to turn pivot table field on and off 1
remove or add items from a pivot tables 1
Format Pivot total rows 2
pivot table range selection 2
syntax in VBA to create a pivot table 2
Pivot table - unwanted lines 2

Top