Adding PivotTable fields with a macro

[kittronald]

I'm trying to add PivotTable fields with a macro, but it keeps coming
out as follows:

With ActiveSheet.PivotTables("PivotTable2").PivotFields("Fruit")
.Orientation = xlRowField
.Position = 4
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Fruit")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Cost")
.Orientation = xlRowField
.Position = 5
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Cost")
.Orientation = xlRowField
.Position = 2
End With


How can I get rid of With and End With and still make it work ?


- Ronald K.

 

[GS]

In order to get rid of With...End With you need to fully qualify each
line of executable code. The purpose of With...End With constructs is
to reduce the amount of DOT processing, and thus render more efficient
code.

Try...

With ActiveSheet.PivotTables("PivotTable2")
With .PivotFields("Fruit")
.Orientation = xlRowField: .Position = 4
.Orientation = xlRowField: .Position = 1
End With
With .PivotFields("Cost")
.Orientation = xlRowField: .Position = 5
.Orientation = xlRowField: .Position = 2
End With
End With

 

[kittronald]

Garry,

Thank you, that worked like a charm !

The only thing I changed was the first .Orientation line for each
field since they appear to be unnecessary.



- Ronald K.

 

[GS]

kittronald laid this down on his screen :

Garry,

Thank you, that worked like a charm !

The only thing I changed was the first .Orientation line for each
field since they appear to be unnecessary.



- Ronald K.

You're welcome! -Always glad to help.

I didn't know why you had 2 .Orientation lines but was only interested
in abbreviating your code to the minimum necessary for the task. Now
it's, as you say, 2 lines less and so all the better!