VBA excel pivot field removal error and Grandtotal disfunction

[David_Neal]

In making a macro to manipulate an excel pivot for final formatting, I've run into 2 strange problems.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
1st, while removing existing pivot fields from the pivot, the macro generates an error 1004 code for 2 lines alone. The rest work just fine. Since I pulled this from a recorded macro, and the formatting/content is the same as the other lines I can't see what's wrong. Perhaps a line length limitation? Code is below, with problem area bolded.<o></o>
ActiveSheet.PivotTables("ross pivot").PivotFields("Beg Qtr Mth 1 Qty (K#)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("ross pivot").PivotFields("Beg Qtr Mth 2 Qty (K#)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("ross pivot").PivotFields("Beg Qtr Mth 3 Qty (K#)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("ross pivot").PivotFields( _
"Beg Qtr Qty before Delq (K#)").Orientation = xlHidden
ActiveSheet.PivotTables("ross pivot").PivotFields("Beg Qtr Delq Qty (K#)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("ross pivot").PivotFields("Beg Qtr Qty with Delq (K#)") _
.Orientation = xlHidden<o></o>
The 2nd problem is that the following instruction to disable Grand Total in the pivot is ignored altogether.<o></o>
ActiveSheet.PivotTables("ross pivot").RowGrand = False<o></o>
<o></o>
Again, this was copied from a recorded macro so not sure how I'm going astray. Any suggestions on fixing, or as to cause, would be very much appreciated.<o></o>
<o></o>
David

 

[David_Neal]

Well, I'm answering my own questions, based on a peer's input.
Whenever you record a macro which hides a calculated field in a pivot, the code recorded doesn’t work. You could argue whether it’s recording bad code, or failing to execute good code, but the net result is the same. An alternative way (that works) of asking Excel to perform the same operation :
ActiveSheet.PivotTables("ross pivot").DataPivotField.PivotItems("Beg Qtr Qty before Delq (K#)").Visible = False

For the 2nd problem, it seems that excel's recording mechanism erroneously records ColumnGrand as RowGrand. Isn't that odd!?

David S