HOW TO: PivotTable.SourceData
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).Addr ess(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 |
HOW TO: PivotTable.SourceData
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).Addr ess(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 |
All times are GMT +1. The time now is 01:08 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com