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
--
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
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.


wacNTN wrote:
> 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


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

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
--
WAC


"Debra Dalgleish" wrote:

> 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.
>
>
> wacNTN wrote:
> > 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
>
>
> --
> Debra Dalgleish
> Contextures
> http://www.contextures.com/tiptech.html
>
>

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
'=================

wacNTN wrote:
> 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


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

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
--
WAC


"Debra Dalgleish" wrote:

> 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
> '=================
>
> wacNTN wrote:
> > 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
>
>
> --
> Debra Dalgleish
> Contextures
> http://www.contextures.com/tiptech.html
>
>

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.


wacNTN wrote:
> 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


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

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
--
WAC


"Debra Dalgleish" wrote:

> 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.
>
>
> wacNTN wrote:
> > 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
>
>
> --
> Debra Dalgleish
> Contextures
> http://www.contextures.com/tiptech.html
>
>