HOW TO: PivotTable.SourceData

[Kevin McCartney]

I've been trying to update the source data range of my pivot tables on all my
worksheets within a workbook and I've been using the following code

pt.SourceData = wsDATA.Name & "!R1C1:" &
wsDATA.Cells.SpecialCells(xlCellTypeLastCell).Address(ReferenceStyle:=xlR1C1)

This works great while its on an English machine but if I change the
Regional Setting e,g, to Germany then it fails because of the xlR1C1
reference style because the returns e.g. R1C1:R4500C62 but German setting
require Z4500S62

So the question is how do I set the source data of a pivot table using the
xlR1C1 style of the machine settings since if I question mark ?pt.SourceData
I get the
xlR1C1 style. Hence if I try to use xlA1 reference style that too fails.

Any help much appreciated

ciao
KM

[Kevin McCartney]

FormulaR1C1Local I think might help

"Kevin McCartney" wrote:

> I've been trying to update the source data range of my pivot tables on all my
> worksheets within a workbook and I've been using the following code
>
> pt.SourceData = wsDATA.Name & "!R1C1:" &
> wsDATA.Cells.SpecialCells(xlCellTypeLastCell).Address(ReferenceStyle:=xlR1C1)
>
> This works great while its on an English machine but if I change the
> Regional Setting e,g, to Germany then it fails because of the xlR1C1
> reference style because the returns e.g. R1C1:R4500C62 but German setting
> require Z4500S62
>
> So the question is how do I set the source data of a pivot table using the
> xlR1C1 style of the machine settings since if I question mark ?pt.SourceData
> I get the
> xlR1C1 style. Hence if I try to use xlA1 reference style that too fails.
>
> Any help much appreciated
>
> ciao
> KM
>