Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Pivot table creation error VBA XLS
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 12
Default Pivot table creation error VBA XLS
Hi,
I have this following error :
"Unable to get the PivotTables property of the Worksheet class" on the With
statement when I launch this macro.

What did I forget to declare ?

"Exploit" is the sheet on which I want the pivot table like this:
Quest_EXT 3
Quest_ITV 3
Quest_LXE 1
Quest_KMQ 2

"Questions" is the sheet of data like this:

COLUMN A
date_week
Quest_EXT
Quest_EXT
Quest_ITV
Quest_EXT
Quest_ITV
Quest_LXE
Quest_ITV
Quest_KMQ
Quest_KMQ



MACRO :


Sub dataexploitboard()
Worksheets("Exploit").Activate
Myfile = "tryagain.xls"
mytable = "Pivot"

ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("Questions").Select
Columns("A:A").Select
destinationtable = "'[" & Myfile & "]Exploit'!R9C1"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Questions!A:A").CreatePivotTable TableDestination:= _
destinationtable, _
TableName:=mytable, DefaultVersion:=xlPivotTableVersion10
With ActiveSheet.PivotTables(mytable).PivotFields("date _week")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables(mytable).AddDataField ActiveSheet.PivotTables( _
mytable).PivotFields("date_week"), "Count of date_week", xlCount
ActiveWorkbook.ShowPivotTableFieldList = False
Range("C11").Select
ActiveCell.FormulaR1C1 = "=RC[-1]"
Range("C12").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+RC[-1]"
Range("C12").Select
Selection.AutoFill Destination:=Range("C12:C39")

End Sub

EXTRA-BALL :
Please do you know how to get the number of items on rows of a pivot table ?
In my exemple, the result would be 4.
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6
Default Pivot table creation error VBA XLS
I'm new to pivot tables and VBA and recently had trouble with the same
error. I hope someone more experienced jumps in, in case I'm wrong
about any of this.

It looks like you tried just what I did: used the macro recording function
to generate this code. That's a great way to start, but for some reason you
apparently can't use the ActiveWorkbook.PivotCaches.Add().CreatePivotTable
method to create a pivot table. You have to use the wizard.

Try replacing your code:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Questions!A:A").CreatePivotTable TableDestination:= _
destinationtable, _
TableName:=mytable, DefaultVersion:=xlPivotTableVersion10

with something like this:

Worksheets(sheet).PivotTableWizard _
SourceType:=xlDatabase, _
SourceData:="Questions!A:A", _
TableDestination:=destinationtable, _
TableName:=mytable, DefaultVersion:=xlPivotTableVersion10

It's working for me.

- Bob

P.S. I'm grateful to Tom Ogilvie who steered me in a successful direction
with this a few weeks ago.


"Willow" wrote in message
...
Hi,
I have this following error :
"Unable to get the PivotTables property of the Worksheet class" on the
With
statement when I launch this macro.

What did I forget to declare ?

"Exploit" is the sheet on which I want the pivot table like this:
Quest_EXT 3
Quest_ITV 3
Quest_LXE 1
Quest_KMQ 2

"Questions" is the sheet of data like this:

COLUMN A
date_week
Quest_EXT
Quest_EXT
Quest_ITV
Quest_EXT
Quest_ITV
Quest_LXE
Quest_ITV
Quest_KMQ
Quest_KMQ



MACRO :


Sub dataexploitboard()
Worksheets("Exploit").Activate
Myfile = "tryagain.xls"
mytable = "Pivot"

ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("Questions").Select
Columns("A:A").Select
destinationtable = "'[" & Myfile & "]Exploit'!R9C1"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Questions!A:A").CreatePivotTable TableDestination:= _
destinationtable, _
TableName:=mytable, DefaultVersion:=xlPivotTableVersion10
With ActiveSheet.PivotTables(mytable).PivotFields("date _week")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables(mytable).AddDataField
ActiveSheet.PivotTables( _
mytable).PivotFields("date_week"), "Count of date_week", xlCount
ActiveWorkbook.ShowPivotTableFieldList = False
Range("C11").Select
ActiveCell.FormulaR1C1 = "=RC[-1]"
Range("C12").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+RC[-1]"
Range("C12").Select
Selection.AutoFill Destination:=Range("C12:C39")

End Sub

EXTRA-BALL :
Please do you know how to get the number of items on rows of a pivot table
?
In my exemple, the result would be 4.


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 12
Default Pivot table creation error VBA XLS
Actually, you're right in some how, the macro recording function was wrong in
some terms, that I fixed (well I thought !) and the tricky point (error)
remains on the With-statement. I also tried your synthax but it didn't
resolve the error, for my deeply misery !!
Many thanks anyway Farrell77 for this awaited help...
I hope somebody encountered this before as well...
Willow.

"farrell77" wrote:

I'm new to pivot tables and VBA and recently had trouble with the same
error. I hope someone more experienced jumps in, in case I'm wrong
about any of this.

It looks like you tried just what I did: used the macro recording function
to generate this code. That's a great way to start, but for some reason you
apparently can't use the ActiveWorkbook.PivotCaches.Add().CreatePivotTable
method to create a pivot table. You have to use the wizard.

Try replacing your code:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Questions!A:A").CreatePivotTable TableDestination:= _
destinationtable, _
TableName:=mytable, DefaultVersion:=xlPivotTableVersion10

with something like this:

Worksheets(sheet).PivotTableWizard _
SourceType:=xlDatabase, _
SourceData:="Questions!A:A", _
TableDestination:=destinationtable, _
TableName:=mytable, DefaultVersion:=xlPivotTableVersion10

It's working for me.

- Bob

P.S. I'm grateful to Tom Ogilvie who steered me in a successful direction
with this a few weeks ago.


"Willow" wrote in message
...
Hi,
I have this following error :
"Unable to get the PivotTables property of the Worksheet class" on the
With
statement when I launch this macro.

What did I forget to declare ?

"Exploit" is the sheet on which I want the pivot table like this:
Quest_EXT 3
Quest_ITV 3
Quest_LXE 1
Quest_KMQ 2

"Questions" is the sheet of data like this:

COLUMN A
date_week
Quest_EXT
Quest_EXT
Quest_ITV
Quest_EXT
Quest_ITV
Quest_LXE
Quest_ITV
Quest_KMQ
Quest_KMQ



MACRO :


Sub dataexploitboard()
Worksheets("Exploit").Activate
Myfile = "tryagain.xls"
mytable = "Pivot"

ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("Questions").Select
Columns("A:A").Select
destinationtable = "'[" & Myfile & "]Exploit'!R9C1"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Questions!A:A").CreatePivotTable TableDestination:= _
destinationtable, _
TableName:=mytable, DefaultVersion:=xlPivotTableVersion10
With ActiveSheet.PivotTables(mytable).PivotFields("date _week")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables(mytable).AddDataField
ActiveSheet.PivotTables( _
mytable).PivotFields("date_week"), "Count of date_week", xlCount
ActiveWorkbook.ShowPivotTableFieldList = False
Range("C11").Select
ActiveCell.FormulaR1C1 = "=RC[-1]"
Range("C12").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+RC[-1]"
Range("C12").Select
Selection.AutoFill Destination:=Range("C12:C39")

End Sub

EXTRA-BALL :
Please do you know how to get the number of items on rows of a pivot table
?
In my exemple, the result would be 4.



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6
Default Pivot table creation error VBA XLS
Okay, maybe somebody more experienced can see what's wrong.
I'll be interested to understand this better.

I suspect the problem is with the CreatePivotTable method in the
statement just above your With statement and that the error on the
With statement occurs because myTable didn't get created.

Good luck!

- Bob


"Willow" wrote in message
...
Actually, you're right in some how, the macro recording function was wrong
in
some terms, that I fixed (well I thought !) and the tricky point (error)
remains on the With-statement. I also tried your synthax but it didn't
resolve the error, for my deeply misery !!
Many thanks anyway Farrell77 for this awaited help...
I hope somebody encountered this before as well...
Willow.

"farrell77" wrote:

I'm new to pivot tables and VBA and recently had trouble with the same
error. I hope someone more experienced jumps in, in case I'm wrong
about any of this.

It looks like you tried just what I did: used the macro recording
function
to generate this code. That's a great way to start, but for some reason
you
apparently can't use the
ActiveWorkbook.PivotCaches.Add().CreatePivotTable
method to create a pivot table. You have to use the wizard.

Try replacing your code:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Questions!A:A").CreatePivotTable TableDestination:= _
destinationtable, _
TableName:=mytable, DefaultVersion:=xlPivotTableVersion10

with something like this:

Worksheets(sheet).PivotTableWizard _
SourceType:=xlDatabase, _
SourceData:="Questions!A:A", _
TableDestination:=destinationtable, _
TableName:=mytable, DefaultVersion:=xlPivotTableVersion10

It's working for me.

- Bob

P.S. I'm grateful to Tom Ogilvie who steered me in a successful
direction
with this a few weeks ago.


"Willow" wrote in message
...
Hi,
I have this following error :
"Unable to get the PivotTables property of the Worksheet class" on the
With
statement when I launch this macro.

What did I forget to declare ?

"Exploit" is the sheet on which I want the pivot table like this:
Quest_EXT 3
Quest_ITV 3
Quest_LXE 1
Quest_KMQ 2

"Questions" is the sheet of data like this:

COLUMN A
date_week
Quest_EXT
Quest_EXT
Quest_ITV
Quest_EXT
Quest_ITV
Quest_LXE
Quest_ITV
Quest_KMQ
Quest_KMQ



MACRO :


Sub dataexploitboard()
Worksheets("Exploit").Activate
Myfile = "tryagain.xls"
mytable = "Pivot"

ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("Questions").Select
Columns("A:A").Select
destinationtable = "'[" & Myfile & "]Exploit'!R9C1"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
"Questions!A:A").CreatePivotTable TableDestination:= _
destinationtable, _
TableName:=mytable, DefaultVersion:=xlPivotTableVersion10
With ActiveSheet.PivotTables(mytable).PivotFields("date _week")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables(mytable).AddDataField
ActiveSheet.PivotTables( _
mytable).PivotFields("date_week"), "Count of date_week",
xlCount
ActiveWorkbook.ShowPivotTableFieldList = False
Range("C11").Select
ActiveCell.FormulaR1C1 = "=RC[-1]"
Range("C12").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+RC[-1]"
Range("C12").Select
Selection.AutoFill Destination:=Range("C12:C39")

End Sub

EXTRA-BALL :
Please do you know how to get the number of items on rows of a pivot
table
?
In my exemple, the result would be 4.





Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,979
Default Pivot table creation error VBA XLS
You should qualify the references, instead of using "ActiveSheet". The
questions sheet was active, and it didn't have a pivot table.

Also, instead of adding a formula to calculate the total, you can
include that in the pivot table.

'==================================
Sub dataexploitboard()
Dim Myfile As String
Dim mytable As String
Dim destinationtable
Dim wsExploit As Worksheet
Dim wsQuestions As Worksheet
Myfile = "tryagain.xls"
mytable = "Pivot"
Set wsExploit = Worksheets("Exploit")
Set wsQuestions = Worksheets("Questions")

destinationtable = "'[" & Myfile & "]Exploit'!R9C1"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Questions!A:A").CreatePivotTable TableDestination:= _
destinationtable, _
TableName:=mytable, DefaultVersion:=xlPivotTableVersion10

wsExploit.PivotTables(mytable).AddDataField wsExploit.PivotTables( _
mytable).PivotFields("date_week"), "Count of date_week", xlCount

With wsExploit.PivotTables(mytable).PivotFields("date_w eek")
.Orientation = xlRowField
.Position = 1
End With

With wsExploit.PivotTables("Pivot").PivotFields("date_w eek")
.Orientation = xlDataField
.Calculation = xlRunningTotal
.BaseField = "date_week"
.Name = "Total"
End With
With wsExploit.PivotTables("Pivot").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With

ActiveWorkbook.ShowPivotTableFieldList = False

End Sub

'======================================

Willow wrote:
Hi,
I have this following error :
"Unable to get the PivotTables property of the Worksheet class" on the With
statement when I launch this macro.

What did I forget to declare ?

"Exploit" is the sheet on which I want the pivot table like this:
Quest_EXT 3
Quest_ITV 3
Quest_LXE 1
Quest_KMQ 2

"Questions" is the sheet of data like this:

COLUMN A
date_week
Quest_EXT
Quest_EXT
Quest_ITV
Quest_EXT
Quest_ITV
Quest_LXE
Quest_ITV
Quest_KMQ
Quest_KMQ



MACRO :


Sub dataexploitboard()
Worksheets("Exploit").Activate
Myfile = "tryagain.xls"
mytable = "Pivot"

ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("Questions").Select
Columns("A:A").Select
destinationtable = "'[" & Myfile & "]Exploit'!R9C1"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Questions!A:A").CreatePivotTable TableDestination:= _
destinationtable, _
TableName:=mytable, DefaultVersion:=xlPivotTableVersion10
With ActiveSheet.PivotTables(mytable).PivotFields("date _week")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables(mytable).AddDataField ActiveSheet.PivotTables( _
mytable).PivotFields("date_week"), "Count of date_week", xlCount
ActiveWorkbook.ShowPivotTableFieldList = False
Range("C11").Select
ActiveCell.FormulaR1C1 = "=RC[-1]"
Range("C12").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+RC[-1]"
Range("C12").Select
Selection.AutoFill Destination:=Range("C12:C39")

End Sub

EXTRA-BALL :
Please do you know how to get the number of items on rows of a pivot table ?
In my exemple, the result would be 4.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 12
Default Pivot table creation error VBA XLS
Debra, that works on my Excel 2003 and this is perfect, but (it would have
been too easy !)my client is running with Excel 97 which don't support all
the methods you use... do you have any idea about change your code in order
to make it work on Excel 97 ?

Oh many thanks in advance

"Debra Dalgleish" wrote:

You should qualify the references, instead of using "ActiveSheet". The
questions sheet was active, and it didn't have a pivot table.

Also, instead of adding a formula to calculate the total, you can
include that in the pivot table.

'==================================
Sub dataexploitboard()
Dim Myfile As String
Dim mytable As String
Dim destinationtable
Dim wsExploit As Worksheet
Dim wsQuestions As Worksheet
Myfile = "tryagain.xls"
mytable = "Pivot"
Set wsExploit = Worksheets("Exploit")
Set wsQuestions = Worksheets("Questions")

destinationtable = "'[" & Myfile & "]Exploit'!R9C1"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Questions!A:A").CreatePivotTable TableDestination:= _
destinationtable, _
TableName:=mytable, DefaultVersion:=xlPivotTableVersion10

wsExploit.PivotTables(mytable).AddDataField wsExploit.PivotTables( _
mytable).PivotFields("date_week"), "Count of date_week", xlCount

With wsExploit.PivotTables(mytable).PivotFields("date_w eek")
.Orientation = xlRowField
.Position = 1
End With

With wsExploit.PivotTables("Pivot").PivotFields("date_w eek")
.Orientation = xlDataField
.Calculation = xlRunningTotal
.BaseField = "date_week"
.Name = "Total"
End With
With wsExploit.PivotTables("Pivot").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With

ActiveWorkbook.ShowPivotTableFieldList = False

End Sub

'======================================

Willow wrote:
Hi,
I have this following error :
"Unable to get the PivotTables property of the Worksheet class" on the With
statement when I launch this macro.

What did I forget to declare ?

"Exploit" is the sheet on which I want the pivot table like this:
Quest_EXT 3
Quest_ITV 3
Quest_LXE 1
Quest_KMQ 2

"Questions" is the sheet of data like this:

COLUMN A
date_week
Quest_EXT
Quest_EXT
Quest_ITV
Quest_EXT
Quest_ITV
Quest_LXE
Quest_ITV
Quest_KMQ
Quest_KMQ



MACRO :


Sub dataexploitboard()
Worksheets("Exploit").Activate
Myfile = "tryagain.xls"
mytable = "Pivot"

ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("Questions").Select
Columns("A:A").Select
destinationtable = "'[" & Myfile & "]Exploit'!R9C1"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Questions!A:A").CreatePivotTable TableDestination:= _
destinationtable, _
TableName:=mytable, DefaultVersion:=xlPivotTableVersion10
With ActiveSheet.PivotTables(mytable).PivotFields("date _week")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables(mytable).AddDataField ActiveSheet.PivotTables( _
mytable).PivotFields("date_week"), "Count of date_week", xlCount
ActiveWorkbook.ShowPivotTableFieldList = False
Range("C11").Select
ActiveCell.FormulaR1C1 = "=RC[-1]"
Range("C12").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+RC[-1]"
Range("C12").Select
Selection.AutoFill Destination:=Range("C12:C39")

End Sub

EXTRA-BALL :
Please do you know how to get the number of items on rows of a pivot table ?
In my exemple, the result would be 4.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,979
Default Pivot table creation error VBA XLS
It's best if you mention the version in your initial post. The following
code should work in Excel 97:

'================================
Sub dataexploitboard97()
Dim Myfile As String
Dim mytable As String
Dim destinationtable
Dim wsExploit As Worksheet
Dim wsQuestions As Worksheet
Myfile = "tryagain.xls"
mytable = "Pivot"
Set wsExploit = Worksheets("Exploit")
Set wsQuestions = Worksheets("Questions")

destinationtable = "'[" & Myfile & "]Exploit'!R9C1"

wsQuestions.PivotTableWizard SourceType:=xlDatabase, _
SourceData:=wsQuestions.Name & "!" & wsQuestions.Cells(1, 1) _
.CurrentRegion.Address(ReferenceStyle:=xlR1C1), _
TableDestination:=destinationtable, TableName:=mytable
wsExploit.PivotTables(mytable).AddFields _
RowFields:=Array("date_week", "Data")
With wsExploit.PivotTables(mytable).PivotFields("date_w eek")
.Orientation = xlDataField
.Name = "QCount"
.Position = 1
End With
With wsExploit.PivotTables(mytable).PivotFields("date_w eek")
.Orientation = xlDataField
.Name = "QTotal"
.Calculation = xlRunningTotal
.BaseField = "date_week"
End With
With wsExploit.PivotTables(mytable).PivotFields("Data")
.Orientation = xlColumnField
.Position = 1
End With
End Sub
'===============================

Willow wrote:
Debra, that works on my Excel 2003 and this is perfect, but (it would have
been too easy !)my client is running with Excel 97 which don't support all
the methods you use... do you have any idea about change your code in order
to make it work on Excel 97 ?

Oh many thanks in advance

"Debra Dalgleish" wrote:


You should qualify the references, instead of using "ActiveSheet". The
questions sheet was active, and it didn't have a pivot table.

Also, instead of adding a formula to calculate the total, you can
include that in the pivot table.

'==================================
Sub dataexploitboard()
Dim Myfile As String
Dim mytable As String
Dim destinationtable
Dim wsExploit As Worksheet
Dim wsQuestions As Worksheet
Myfile = "tryagain.xls"
mytable = "Pivot"
Set wsExploit = Worksheets("Exploit")
Set wsQuestions = Worksheets("Questions")

destinationtable = "'[" & Myfile & "]Exploit'!R9C1"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Questions!A:A").CreatePivotTable TableDestination:= _
destinationtable, _
TableName:=mytable, DefaultVersion:=xlPivotTableVersion10

wsExploit.PivotTables(mytable).AddDataField wsExploit.PivotTables( _
mytable).PivotFields("date_week"), "Count of date_week", xlCount

With wsExploit.PivotTables(mytable).PivotFields("date_w eek")
.Orientation = xlRowField
.Position = 1
End With

With wsExploit.PivotTables("Pivot").PivotFields("date_w eek")
.Orientation = xlDataField
.Calculation = xlRunningTotal
.BaseField = "date_week"
.Name = "Total"
End With
With wsExploit.PivotTables("Pivot").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With

ActiveWorkbook.ShowPivotTableFieldList = False

End Sub

'======================================

Willow wrote:

Hi,
I have this following error :
"Unable to get the PivotTables property of the Worksheet class" on the With
statement when I launch this macro.

What did I forget to declare ?

"Exploit" is the sheet on which I want the pivot table like this:
Quest_EXT 3
Quest_ITV 3
Quest_LXE 1
Quest_KMQ 2

"Questions" is the sheet of data like this:

COLUMN A
date_week
Quest_EXT
Quest_EXT
Quest_ITV
Quest_EXT
Quest_ITV
Quest_LXE
Quest_ITV
Quest_KMQ
Quest_KMQ



MACRO :


Sub dataexploitboard()
Worksheets("Exploit").Activate
Myfile = "tryagain.xls"
mytable = "Pivot"

ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("Questions").Select
Columns("A:A").Select
destinationtable = "'[" & Myfile & "]Exploit'!R9C1"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Questions!A:A").CreatePivotTable TableDestination:= _
destinationtable, _
TableName:=mytable, DefaultVersion:=xlPivotTableVersion10
With ActiveSheet.PivotTables(mytable).PivotFields("date _week")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables(mytable).AddDataField ActiveSheet.PivotTables( _
mytable).PivotFields("date_week"), "Count of date_week", xlCount
ActiveWorkbook.ShowPivotTableFieldList = False
Range("C11").Select
ActiveCell.FormulaR1C1 = "=RC[-1]"
Range("C12").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+RC[-1]"
Range("C12").Select
Selection.AutoFill Destination:=Range("C12:C39")

End Sub

EXTRA-BALL :
Please do you know how to get the number of items on rows of a pivot table ?
In my exemple, the result would be 4.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 12
Default Pivot table creation error VBA XLS
Sounds like you're a master, hopefully I don't have your email !!!!
Many many thanks Debra.




"Debra Dalgleish" wrote:

It's best if you mention the version in your initial post. The following
code should work in Excel 97:

'================================
Sub dataexploitboard97()
Dim Myfile As String
Dim mytable As String
Dim destinationtable
Dim wsExploit As Worksheet
Dim wsQuestions As Worksheet
Myfile = "tryagain.xls"
mytable = "Pivot"
Set wsExploit = Worksheets("Exploit")
Set wsQuestions = Worksheets("Questions")

destinationtable = "'[" & Myfile & "]Exploit'!R9C1"

wsQuestions.PivotTableWizard SourceType:=xlDatabase, _
SourceData:=wsQuestions.Name & "!" & wsQuestions.Cells(1, 1) _
.CurrentRegion.Address(ReferenceStyle:=xlR1C1), _
TableDestination:=destinationtable, TableName:=mytable
wsExploit.PivotTables(mytable).AddFields _
RowFields:=Array("date_week", "Data")
With wsExploit.PivotTables(mytable).PivotFields("date_w eek")
.Orientation = xlDataField
.Name = "QCount"
.Position = 1
End With
With wsExploit.PivotTables(mytable).PivotFields("date_w eek")
.Orientation = xlDataField
.Name = "QTotal"
.Calculation = xlRunningTotal
.BaseField = "date_week"
End With
With wsExploit.PivotTables(mytable).PivotFields("Data")
.Orientation = xlColumnField
.Position = 1
End With
End Sub
'===============================

Willow wrote:
Debra, that works on my Excel 2003 and this is perfect, but (it would have
been too easy !)my client is running with Excel 97 which don't support all
the methods you use... do you have any idea about change your code in order
to make it work on Excel 97 ?

Oh many thanks in advance

"Debra Dalgleish" wrote:


You should qualify the references, instead of using "ActiveSheet". The
questions sheet was active, and it didn't have a pivot table.

Also, instead of adding a formula to calculate the total, you can
include that in the pivot table.

'==================================
Sub dataexploitboard()
Dim Myfile As String
Dim mytable As String
Dim destinationtable
Dim wsExploit As Worksheet
Dim wsQuestions As Worksheet
Myfile = "tryagain.xls"
mytable = "Pivot"
Set wsExploit = Worksheets("Exploit")
Set wsQuestions = Worksheets("Questions")

destinationtable = "'[" & Myfile & "]Exploit'!R9C1"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Questions!A:A").CreatePivotTable TableDestination:= _
destinationtable, _
TableName:=mytable, DefaultVersion:=xlPivotTableVersion10

wsExploit.PivotTables(mytable).AddDataField wsExploit.PivotTables( _
mytable).PivotFields("date_week"), "Count of date_week", xlCount

With wsExploit.PivotTables(mytable).PivotFields("date_w eek")
.Orientation = xlRowField
.Position = 1
End With

With wsExploit.PivotTables("Pivot").PivotFields("date_w eek")
.Orientation = xlDataField
.Calculation = xlRunningTotal
.BaseField = "date_week"
.Name = "Total"
End With
With wsExploit.PivotTables("Pivot").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With

ActiveWorkbook.ShowPivotTableFieldList = False

End Sub

'======================================

Willow wrote:

Hi,
I have this following error :
"Unable to get the PivotTables property of the Worksheet class" on the With
statement when I launch this macro.

What did I forget to declare ?

"Exploit" is the sheet on which I want the pivot table like this:
Quest_EXT 3
Quest_ITV 3
Quest_LXE 1
Quest_KMQ 2

"Questions" is the sheet of data like this:

COLUMN A
date_week
Quest_EXT
Quest_EXT
Quest_ITV
Quest_EXT
Quest_ITV
Quest_LXE
Quest_ITV
Quest_KMQ
Quest_KMQ



MACRO :


Sub dataexploitboard()
Worksheets("Exploit").Activate
Myfile = "tryagain.xls"
mytable = "Pivot"

ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("Questions").Select
Columns("A:A").Select
destinationtable = "'[" & Myfile & "]Exploit'!R9C1"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Questions!A:A").CreatePivotTable TableDestination:= _
destinationtable, _
TableName:=mytable, DefaultVersion:=xlPivotTableVersion10
With ActiveSheet.PivotTables(mytable).PivotFields("date _week")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables(mytable).AddDataField ActiveSheet.PivotTables( _
mytable).PivotFields("date_week"), "Count of date_week", xlCount
ActiveWorkbook.ShowPivotTableFieldList = False
Range("C11").Select
ActiveCell.FormulaR1C1 = "=RC[-1]"
Range("C12").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+RC[-1]"
Range("C12").Select
Selection.AutoFill Destination:=Range("C12:C39")

End Sub

EXTRA-BALL :
Please do you know how to get the number of items on rows of a pivot table ?
In my exemple, the result would be 4.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html


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 table creation from another pivot table in 2007 Roger Excel Discussion (Misc queries) 0 August 4th 09 10:52 PM
Pivot Table - there is an error message pop out during creation. Cpviv Charts and Charting in Excel 1 October 31st 08 05:31 AM
Pivot table creation in excel NRCan13 Excel Worksheet Functions 1 October 1st 08 02:07 PM
pivot table creation procedures PIVOT TABLE Excel Discussion (Misc queries) 1 July 2nd 08 08:40 AM
pivot table creation in shared worksheet dave in Toronto Excel Discussion (Misc queries) 1 August 9th 07 11:58 PM


All times are GMT +1. The time now is 09:40 AM.

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