Pivot Table Data Source, point to cell?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

J.W. Aldridge - 26 Mar 2008 21:46 GMT

Is there any way to point the data source block to a cell.

I want to simply update a cell (i.e. A1) with the range instead having
to open the pivot table and changing from the wizard. Is there a way
to do this?

Example:

Cell A1 contains: '[Output 3.24.xls]FORMALS-OUT'!$A$5:$P$65536

Data source in pivot table points to cell A1 to get the range.

please tell me there's a way to do this....

thanx.!

Reply to this Message

Héctor Miguel - 27 Mar 2008 06:13 GMT

hi, Jeremy !

> Is there any way to point the data source block to a cell.
> I want to simply update a cell (i.e. A1) with the range instead having to open the pivot table
[quoted text clipped - 3 lines]
> Data source in pivot table points to cell A1 to get the range.
> please tell me there's a way to do this...

1) add another -initial- apostrophe to A1 cell so it "reads" (in formula bar):
''[Output 3.24.xls]FORMALS-OUT'!$A$5:$P$65536

2) use something like the following instruction
(either in a sub procedure or from immediate window pane -vba editor-)

activesheet.pivottables(1).sourcedata = application.convertformula([a1],xla1,xlr1c1)

note: convertformula needs some considerations regarding international use/conventions
RC (Row/Column) might be different to other users (also, excel's language/versions)

hth,
hector.

Reply to this Message

Rate this thread: