E
Eric Lecocq
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:=xlDatabase, _
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
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:=xlDatabase, _
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