Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
pivot and vba !!!
the following code works perfect till thre comment pivot table 2
i receive an error invalid call or reference can you help me ? ele Sub makepivot() Dim wb1 As Workbook Dim sht1, sht2 As Worksheet Dim pc As PivotCache Dim pt As PivotTable Set wb1 = ActiveWorkbook Set sht1 = wb1.Sheets(1) sht1.Name = "Blue Planet" Set pc = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _ SourceData:="blue planet!A:N") ' ' Pivot table 1 (Per Code) ' Application.StatusBar = "Creating First Pivot..." pc.CreatePivotTable TableDestination:="", TableName:="PivotTable1" With ActiveSheet.PivotTables("PivotTable1") .ColumnGrand = False .RowGrand = False .SmallGrid = False End With ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array( _ "Sold-to Name", "Data"), ColumnFields:="Date", PageFields:="Material" With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Demand") .Orientation = xlDataField .Caption = "_Demand" .Position = 1 .Function = xlSum End With With ActiveSheet.PivotTables("pivottable1").PivotFields ("Allocation") .Orientation = xlDataField .Caption = "_Allocation" .Function = xlSum End With ActiveSheet.PivotTables("pivottable1").PivotFields ("Material").CurrentPage = "3EC17385AA" ================================================== ============= ' ' Pivot table 2 (Per Country) ' Application.StatusBar = "Creating Second Pivot..." pc.CreatePivotTable TableDestination:="", TableName:="pivottable2" With ActiveSheet.PivotTables("pivottable2") .ColumnGrand = False .RowGrand = False .SmallGrid = False End With ActiveSheet.PivotTables("pivottable2").AddFields RowFields:=Array( _ "Material", "Data"), ColumnFields:="Date", PageFields:="Sold-to Name" With ActiveSheet.PivotTables("pivottable2").PivotFields ("Demand") .Orientation = xlDataField .Caption = "_Demand" .Position = 1 .Function = xlSum End With With ActiveSheet.PivotTables("pivottable2").PivotFields ("Allocation") .Orientation = xlDataField .Caption = "_Allocation" .Function = xlSum End With ActiveSheet.PivotTables("pivottable2").PivotFields ("Sold-to Name").CurrentPage = "ALCATEL ITALIE SPA" ================================================== =============== End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot tables - Editing the data source shared by multiple pivot ta | Excel Discussion (Misc queries) | |||
two pivot q's - get rid og getpivotdata and pivot based on pivot | Excel Discussion (Misc queries) | |||
Pivot Table Data Adding contents of two pivot tables and param que | Excel Discussion (Misc queries) | |||
Average Calculations from Pivot Tables - Get Pivot Data? Calc Fiel | Excel Worksheet Functions | |||
How does the term 'pivot' apply to Excel's Pivot tables and Pivot. | Excel Discussion (Misc queries) |