ALL;TOTAL reference help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

wacNTN - 27 Oct 2006 20:50 GMT

I have just started trying to do some VBA for Pivottables. I have recorded
some macros and have a good reference book but for I don't undersand what
ALL;TOTAL refers to and are there other parameters I can use there? If so
what are they, what are they called, what should I search for?
An example line of code follows.

ActiveSheet.PivotTables("PT_ActivityCosts").PivotSelect "'Activity
Type'[ALL;TOTAL]", x1LabelOnly

Signature

WAC

Reply to this Message

Debra Dalgleish - 28 Oct 2006 04:19 GMT

In that example, you're selecting the label in the subtotal row for all
the Activity Type items. If Cancel is an Activity Type, you could change
the line to:

ActiveSheet.PivotTables("PT_ActivityCosts").PivotSelect "'Activity
Type'[Cancel;TOTAL]", x1LabelOnly

and the label in the Cancel subtotal row would be selected.
Change the line to:

ActiveSheet.PivotTables("PT_ActivityCosts").PivotSelect "'Activity
Type'[Cancel]", x1LabelOnly

and the Cancel label cells would be selected.

> I have just started trying to do some VBA for Pivottables. I have recorded
> some macros and have a good reference book but for I don't undersand what
[quoted text clipped - 4 lines]
> ActiveSheet.PivotTables("PT_ActivityCosts").PivotSelect "'Activity
> Type'[ALL;TOTAL]", x1LabelOnly

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message

wacNTN - 30 Oct 2006 21:11 GMT

Debra,

This was very helpful. Thanks! More questions though.

1. What other parameters besides TOTAL can be used? Is there a reference
somewhere? How do I select the GrandTotal rows?

2. My activity types are "1. Base", "2. Projects", "3. Overhead". VBA
doesn't seem to like these types. Are the periods getting in the way?

3. I want to only fill the cell with the actual lable text in it but
everything I have tried fills all the row field cells.

4. Is there a way to format the top header, where the buttons are? Right
now I am just selecting a range and formatting it but it's location can vary
depending on the PT being formatted.

Thanks for any and all help you can provide.

Walter

Signature

WAC

> In that example, you're selecting the label in the subtotal row for all
> the Activity Type items. If Cancel is an Activity Type, you could change
[quoted text clipped - 19 lines]
> > ActiveSheet.PivotTables("PT_ActivityCosts").PivotSelect "'Activity
> > Type'[ALL;TOTAL]", x1LabelOnly

Reply to this Message

Debra Dalgleish - 31 Oct 2006 01:07 GMT

Walter,

1. I don't know of a reference, but if you record the steps
(Tools>Macro>Record New Macro), you may get the examples you need.

2. If you enclose the item names in single quotes, they should work.

3. You could colour the first cell in the selection, e.g.:

Selection.Cells(1, 1).Interior.ColorIndex = 35

4. If you want to format the field buttons:

'===============
Dim pt As PivotTable
Dim pf As PivotField

Set pt = ActiveSheet.PivotTables(1)

On Error Resume Next
For Each pf In pt.PivotFields
pf.LabelRange.Interior.ColorIndex = 40
Next pf
'=================

> Debra,
>
[quoted text clipped - 16 lines]
>
> Walter

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message

wacNTN - 31 Oct 2006 15:31 GMT

Debra,

Thanks again.

On #1, when I record the steps to format the grand total line, the recorded
steps don't include any pivottable specific code. It just looks like
normal range select and selection format code. Any ideas?

When I format the top header, I also format the data selection cell which
sits above the labels for the data area. That cell sticks out like a sore
thumb now. Any ideas?

Thanks,

Walter

Signature

WAC

> Walter,
>
[quoted text clipped - 41 lines]
> >
> > Walter

Reply to this Message

Debra Dalgleish - 31 Oct 2006 18:44 GMT

To capture the correct code, enable selection should be turned on.
To enable selection --
From the Pivot toolbar, choose PivotTable>Select
If it's not already activated, click on Enable Selection

To select a section of a pivot table, e.g. subtotals --
Move the pointer to the left of a subtotal heading in the pivot table.
When the black arrow appears (like the one that appears when the pointer
is over a row button), click to select the subtotal rows in the pivot
table.

For the header formatting, I'm not clear on what you're trying to achieve.

> Debra,
>
[quoted text clipped - 11 lines]
>
> Walter

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message

wacNTN - 31 Oct 2006 22:08 GMT

Debra,

The tip on Enable Selection really helped. I kept wondering why this worked
sometimes and sometimes it didn't. I guess I was turning that off.

Here's what was recorded.

ActiveSheet.PivotTables(sPT1(x)).PivotSelect "'Column Grand Total'",
xlDataAndLabel, True

So the tag is "Column Grand Total"

On the other subject, I want to format the "header" labels for the Row
fields. I have three of them and three data fields for a total of six
columns in my PT. I want to format these six cells using VBA. I currently
use a fixed range but in other spreadsheets these could be in difference
locations. I want to make my code as reusable as possible. Any ideas how
to select these?

Thanks again!

Walter

Signature

WAC

> To capture the correct code, enable selection should be turned on.
> To enable selection --
[quoted text clipped - 24 lines]
> >
> > Walter

Reply to this Message