Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > pivot and vba !!!
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8
Default 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



Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default pivot and vba !!!
since you specify "" for the destination in each case, maybe this causes a
conflict. Perhaps you need to actually specify a destination.

--
Regards,
Tom Ogilvy

"Eric Lecocq" wrote in message
...
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





Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 473
Default pivot and vba !!!
Eric Lecocq wrote:
the following code works perfect till thre comment pivot table 2
i receive an error invalid call or reference


On what line is the error reported?
I don't recognise the error message - what error number and what
precise wording?

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8
Default pivot and vba !!!
I thought that specifying "" means a new sheet ?
I will try to create a new sheet and then create the pivottable.

Ele




"Tom Ogilvy" wrote in message
...
since you specify "" for the destination in each case, maybe this causes a
conflict. Perhaps you need to actually specify a destination.

--
Regards,
Tom Ogilvy

"Eric Lecocq" wrote in message
...
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







Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8
Default pivot and vba !!!
line is pc.CreatePivotTable TableDestination:="", TableName:="pivottable2"

error is Run-time error '1004': Application-defined or object-defined error
....

thanks for your help.

Ele

"Bill Manville" wrote in message
...
Eric Lecocq wrote:
the following code works perfect till thre comment pivot table 2
i receive an error invalid call or reference


On what line is the error reported?
I don't recognise the error message - what error number and what
precise wording?

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8
Default pivot and vba !!!
it succeeds but i can't understand why !!!
thank you very much.

Ele.


"Eric Lecocq" wrote in message
...
I thought that specifying "" means a new sheet ?
I will try to create a new sheet and then create the pivottable.

Ele




"Tom Ogilvy" wrote in message
...
since you specify "" for the destination in each case, maybe this causes
a
conflict. Perhaps you need to actually specify a destination.

--
Regards,
Tom Ogilvy

"Eric Lecocq" wrote in message
...
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









Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot tables - Editing the data source shared by multiple pivot ta sankat Excel Discussion (Misc queries) 1 April 22nd 10 03:05 PM
two pivot q's - get rid og getpivotdata and pivot based on pivot Light Excel Discussion (Misc queries) 1 December 4th 09 01:07 AM
Pivot Table Data Adding contents of two pivot tables and param que Roundy Excel Discussion (Misc queries) 0 July 2nd 07 10:20 PM
Average Calculations from Pivot Tables - Get Pivot Data? Calc Fiel westy Excel Worksheet Functions 5 March 10th 07 01:31 AM
How does the term 'pivot' apply to Excel's Pivot tables and Pivot. stvermont Excel Discussion (Misc queries) 1 February 17th 05 01:34 AM


All times are GMT +1. The time now is 02:47 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement