Hi,

I have a pivot table based on a worksheet range. I would
like to be able to change the source range thru VBA. I
know I can use the PivotTableWizard method, but I would
prefer to manipulate the appropriate objects and
properties myself. All the help topics I can find only
use the PivotTableWizard. Is there some other way that I
can use to enlarge or shrink the range on a worksheet that
the pivot table gets it's data from ?

Any assistance appreciated.

RodT.

Hi
as a starting point: Record I macro while you're doing this manually.

--
Regards
Frank Kabel
Frankfurt, Germany


Rod wrote:
Hi,

I have a pivot table based on a worksheet range. I would
like to be able to change the source range thru VBA. I
know I can use the PivotTableWizard method, but I would
prefer to manipulate the appropriate objects and
properties myself. All the help topics I can find only
use the PivotTableWizard. Is there some other way that I
can use to enlarge or shrink the range on a worksheet that
the pivot table gets it's data from ?

Any assistance appreciated.

RodT.

Thanks,

However, it uses the PivotTableWizard. Not the result
that I am after.

Have fun,

Rod.

-----Original Message-----
Hi
as a starting point: Record I macro while you're doing
this manually.

--
Regards
Frank Kabel
Frankfurt, Germany


Rod wrote:
Hi,

I have a pivot table based on a worksheet range. I
would
like to be able to change the source range thru VBA. I
know I can use the PivotTableWizard method, but I would
prefer to manipulate the appropriate objects and
properties myself. All the help topics I can find only
use the PivotTableWizard. Is there some other way
that I
can use to enlarge or shrink the range on a worksheet
that
the pivot table gets it's data from ?

Any assistance appreciated.

RodT.
.

The best way around this that I have found is to set up
your Pivot table range as a named range. Then I use code
to adjust the address of the named range and refresh the
pivot table.
Diane

-----Original Message-----
Hi,

I have a pivot table based on a worksheet range. I
would
like to be able to change the source range thru VBA. I
know I can use the PivotTableWizard method, but I would
prefer to manipulate the appropriate objects and
properties myself. All the help topics I can find only
use the PivotTableWizard. Is there some other way that
I
can use to enlarge or shrink the range on a worksheet
that
the pivot table gets it's data from ?

Any assistance appreciated.

RodT.
.

The easiest way may be to use a dynamic range.

Debra Dalgleish has some nice notes at:
http://www.contextures.com/xlNames01.html#Dynamic



Rod wrote:

Hi,

I have a pivot table based on a worksheet range. I would
like to be able to change the source range thru VBA. I
know I can use the PivotTableWizard method, but I would
prefer to manipulate the appropriate objects and
properties myself. All the help topics I can find only
use the PivotTableWizard. Is there some other way that I
can use to enlarge or shrink the range on a worksheet that
the pivot table gets it's data from ?

Any assistance appreciated.

RodT.

--

Dave Peterson