Formatting Pivottable Objects with VBA (Office 97)

Topic

New Reply

Hi!

I have a spreadsheet with several pivottables and some VBA that performs various operations on them, including changing the source of data and resetting the formatting.

One of the parts of the pivottable that I want to reformat is the row subtotals, but I can’t work out how to specify the object.

As an example, I can use the following code to format the rows themselves:

    objSheet.PivotTables(1).RowFields("Field1").DataRange.Interior.ColorIndex = 15

How can I do that kind of thing to subtotals?

Hope that’s clear. Thank you to anyone who responds.

Reply | Quote

Replies

In Excel 2002 you can do something like this, but I don’t know whether it works in Excel 97:

Application.PivotTableSelection = True
ActiveSheet.PivotTables(1).PivotSelect “Field1[All;Total]”, xlDataAndLabel, True
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With

Reply | Quote

Yes, it does work in Excel 97, but only if you remove the “, True” argument at the end.

I was really hoping to modify the object directly, rather than have to make a selection, but it really does look like that’s the only solution.

Thanks you, Hans!

Reply | Quote

In general, I try to avoid selecting cells in a macro, but I don’t think it can be avoided here – there doesn’t appear to be an equivalent of RowRange or PageRange for (sub)totals in the object model.

Reply | Quote