Solved Second pivot table with PivotTableWizard VBS

February 11, 2014 at 00:39:27
Specs: Windows XP

i have problem with second pivot table in my vbscript.

in excel are two same sheets with same column names. sheets are sht1 and sht8.
only difference is
sht1 (TPN_Day) = daily data
sht8 (TPN_WTD) = weekly data

as first i insert column names to sht1, after that column names are copied to sht8
cb.sheets("TPN_Day").range("A1:E6").copy cb.sheets("TPN_WTD").range("A1")

for pivot table for data from sht1 i use
XL1data = "Pivot1"
sht1.PivotTableWizard SourceType=xlDatabase,xlApp.Range(sht1.range("A1").currentregion.address),"TPN_Day!R8C1:R20C8","Pivot1"

sht3.PivotTables(XL1data).PivotFields("Store").Orientation = 1
sht3.PivotTables(XL1data).PivotFields("Store name").Orientation = 1
sht3.PivotTables(XL1data).PivotFields("Division code").Orientation = 1
sht3.PivotTables(XL1data).PivotFields("Department code").Orientation = 1
sht3.PivotTables(XL1data).PivotFields("Section code").Orientation = 1
sht3.PivotTables(XL1data).PivotFields("RC stock loss").Orientation = 2

With sht3.PivotTables(XL1data).PivotFields("Stock loss")
.Orientation = 4
.Function = xlSum
.numberformat = "# ### ##0.00_);[Red]-# ### ##0.00"
end with

it's finish every time successfully,
but when i want to do same for sht8 it display error message:
Microsoft Office Excel: The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns, if you are changing the name of a PivotTable filed, you must type a new name for the field.

its strange because i use same script
XL2data = "Pivot2"
sht8.PivotTableWizard SourceType=xlDatabase,xlApp.Range(sht8.range("A1").currentregion.address),"TPN_WTD!R8C1:R20C8","Pivot1"

sht9.PivotTables(XL2data).PivotFields("Store").Orientation = 1
sht9.PivotTables(XL2data).PivotFields("Store name").Orientation = 1
sht9.PivotTables(XL2data).PivotFields("Division code").Orientation = 1
sht9.PivotTables(XL2data).PivotFields("Department code").Orientation = 1
sht9.PivotTables(XL2data).PivotFields("Section code").Orientation = 1
sht9.PivotTables(XL2data).PivotFields("RC stock loss").Orientation = 2

With sht9.PivotTables(XL2data).PivotFields("Stock loss")
.Orientation = 4
.Function = xlSum
.numberformat = "# ### ##0.00_);[Red]-# ### ##0.00"
end with

could you pls help me resolve this problem thx.

See More: Second pivot table with PivotTableWizard VBS

Report •

April 7, 2014 at 05:29:04
✔ Best Answer
i solved it. problem was with SourceType setup. i remove .address at the end and it's function now

message edited by Peter2285

Report •
Related Solutions

Ask Question