Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
pivot table
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" Set sht2 = ActiveSheet sht2.Name = "Per Code" sht2.Activate Cells.Select Selection.Font.Size = 8 ActiveWindow.Zoom = 75 ' ' 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" Set sht2 = ActiveSheet sht2.Name = "Per Country" sht2.Activate Cells.Select Selection.Font.Size = 8 ActiveWindow.Zoom = 75 End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
Copying values from pivot table to cells outside pivot table | Excel Discussion (Misc queries) | |||
Filter lines with Pivot table and non pivot table columns | Charts and Charting in Excel | |||
Filter lines with Pivot table and non Pivot table columns | Excel Discussion (Misc queries) | |||
Help required with setting up a pivot table with the source on sheet1 to have the pivot table created on sheet called "report" | Excel Programming |